Forum Discussion
Power Query Table != Power BI Table
Replicated the query on a csv extract, no problems if it's a connection to csv. Something with the connection/connector is bugged.
It turns out that this particular connector repulls the query at each applied step, and as the query randomizes the data pull every time it breaks the order of items. The solution is to sort at the very top of the query to lock the sort order. This negatively impacts query refreshing and editing quite a bit so heads up on that.
HI SBuri,
Please try increasing the data cache to the maximum limit possible depending on the space in your local machine.
As far as I know if set more than 8 GB, there should ne no issue regarding memory.
Navigation: File -> Option & Settings -> Options -> Data Cache Management Options (Increase this to more than 8 GB)
Regards,
Saurabh Kedia
Thank you,
I did try this, unfortunately it didn't resolve the error. I'm not dealing with too much data from what I've heard. 19.3mill rows and I've read of people dealing with over 100mill so I wouldn't jump to a memory error conclusion except for the table suddenly fixing itself.
A couple developments.
1) I rebuilt the same data structure using a small set of dummy data and it works flawlessly.
2) I discovered that in Power Query now things are loading correctly. For instance 02LD maps to ColA 02, ColB "LD", and this traces back to key 1 correctly. However, when I save and load this past Power Query and look at key 1 in the data tables it shows completely different values like 79RR instead of 02LD. I'm completely confused as to how these two could conflict with one another outside of a bug. I mean after I press Save and Load from Power Query the data table should be the same in both places right?
I believe this is some error with PBI. I don't think there is any logical reason the values would be completely correct in Power Query, and then when saving and loading the values would shift to be incorrect, such that key 0 maps to value LZ in PQ, but key 0 maps to CW in the PBI data table after saving and loading from Power Query.
I also discovered Power BI is changing the sort order for every single step in Power Query, even though each step is not giving a sort command.
Replicated the query on a csv extract, no problems if it's a connection to csv. Something with the connection/connector is bugged.