The problem is how Power Query treats empty, blank and null entries. We see empty and blank cells as blanks in the data whereas null cells have the word null in them. Power Query will always fill down and replace null cells. The screen shot below is from a CSV file. That 'helper table' is loaded simply through the 'From Table' button on the Data ribbon.
It is created with the option 'Connection Only' - and I have tried both checking and un-checking Load to DM with same results. But now, on exiting Power Query via Load To I mean even after 15 - 20 minutes, it is still showing Loading Data So reverting back to the Power Query I replace my previous edit, and just hard-code the same filter as before.
Now the table loads to Data Model thankfully the measures etc I had coded are still there. To me, this seems definitive proof -- when the Power Query contains a step referencing a table within the Workbook, then it doesn't load to the Data Model, but will load to a table only. I will try some smaller examples to explore that for reproducibility. Assuming it does reproduce -- is there a place to report this as a bug or issue? Wyn Hopkins sorry for the delayed response. My original file is way too big and in any case contains non-public information which is not appropriate to share.
I made a tiny version with a self-contained table created in PQ code, but that actually filters fine against a table from a Worksheet cell range, and loads to the DM just fine. I also have a toy version of the data, this is a completely separate file, created from scratch which embodies the key data steps as the main file.
Unfortunately this still is quite large. However it exhibits the exact same behaviour - loading to Table just fine, but just hanging on load to DM. I can probably make a Zip with the Toy file and the data files in a subsidiary folder if that helps.
Wyn Hopkins you may well be sorry you asked, but here goes. So I don't have an immediate issue, just a nagging sense of something not being quite right. Note - this is a 'toy' example using smaller data-sets that I had built just to try out filtering techniques in PQ. But essentially identical to what I was doing on the full data-set. There are 2 key Queries A : Load from folder of CSV files, do some basic filtering and manipulation, resulting in a table.
Query B takes that table and does some more complicated operation, the key is using a table loaded from cells in the workbook to test a column in the table for presence of text strings. IN checking this for reproducibility, I found that with a very small data set 2 files each of 10 rows , everything worked fine and I could load to the DM no problem. When I increased the size 10 files, each 10, rows then :.
Query B worked on load to Table or connection only , but ticking the 'Load to DM' meant the query never refreshed.
SelectRows Source, each [Attributes]? Products 74 Special Topics 42 Video Hub Most Active Hubs Microsoft Teams. Security, Compliance and Identity. Learn more.
Power Query won't read from. Asked 4 years ago. Active 4 years ago. Viewed 4k times. Improve this question. What does your query look like? You can see this by going to the Advanced Editor. If you make a new query and call Excel. Workbook with a path to an. The error you are seeing occurs when Power Query can't find the row with the cell values specified. Every other folder on any drive I've tried so far.
As I write this it sounds so bizarre that I had to go back and verify this behavior. As expected I get the invalid connection string error. I don't restart Excel it just simply works. I just found the problem. The semi-colon in the project folder name was causing the problem.
When I removed the semi-colon from the folder name I stopped getting the error. Products 74 Special Topics 42 Video Hub Most Active Hubs Microsoft Teams. Security, Compliance and Identity. Microsoft Edge Insider. I suggest you restart the computer, close all anti-virus and third-party software and then launch Excel in the normal mode to do a test to see the result.
Now it occurs again. I can think of no possible difference what might caused the symptoms disappear and reappear. The latest version is Version Build Meanwhile, check if the issue happens only in the specific files. Please restart the computer, close all anti-virus and third-party software and then launch Excel in the normal mode to do a test to see and let us know the result.
Unfortunately I can not install any unauthorized update on my company laptop and the latest authorized version is Version Build I am also unable to close all anti-virus software as I have no admin privileges. I can confirm however that the issue occurs in multiple, non related files, not only in a specific file. Generally, we need to focus on the latest version of the Office applications for troubleshooting, so I suggest you contact your administrator first do a test on a computer with the latest version of the Excel application installed to see the result.
If the issue goes away, I suggest you let your administrator update the application. Moreover, to exclude other scenarios, I also suggest you get an administrator account to log into the computer, close all anti-virus and third-party software and then launch Excel in the normal mode to do a test to see and let us know the result.
My Administrator is bound by company policy regarding what we can install.
0コメント