cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SBuri Resolver II
Resolver II

Power Query Table != Power BI Table

Hi,

 

 

I have a table loaded in 3 different places in one Power BI file:

1) In Power Query - produces expected results

2) In Power BI Data Table views - produces expected results

3) In Power BI Visualizations as a table - produces incorrect results

 

I have taken several steps to try to identify why this is occuring:

1) Removed all relationships

2) Removed all calculations, only using columns

3) Only using columns from this one table, removed any relational columns

 

This issue manifests itself in two ways:

1 - In tables 1), 2), I can see ex. record #135,093. In Table 3) that record is gone.

2 - In tables 1), 2), record ###,### clearly maps to a value like "LK". In Table 3) that same record may map to ZC, DF, etc

 

How in the world is it possible to have a single table, no relationships, no measures, just pulling the columns, and for there to be a schism in the data like this? I would provide the file but the data is sensitive.

 

Thanks to anyone who can shed light on what this might be.

2 ACCEPTED SOLUTIONS

Accepted Solutions
SBuri Resolver II
Resolver II

Re: 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.

View solution in original post

SBuri Resolver II
Resolver II

Re: Power Query Table != Power BI Table

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. 

View solution in original post

8 REPLIES 8
Microsoft
Microsoft

Re: Power Query Table != Power BI Table

Hi @SBuri

 

In table visual, you may check the aggregate for the values. Below is the document about it for your reference. If it is not your case, please share more details about your scenario or send me the file via private message.

https://docs.microsoft.com/en-us/power-bi/service-aggregates

1.png

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SBuri Resolver II
Resolver II

Re: Power Query Table != Power BI Table

Hi Cherie,

 

You're partially right, I realized yesterday that Power BI automatically changed that column input to sum. Previously, I believe Power BI would change the field name to "Sum of {FieldName}". After changing it, I was able to find the record, this solves issue #1.

 

The second issue eludes me however. To give more details - I generate a key involving several columns. For ex:

- Key = Concatenate(ColA, ColB) (not with this syntax though), and I can see the key generated.

- Given the following inputs:

-- ColA, Row1 = 06

-- ColB, Row1 = "RZ"

-- Key = 06RZ

 

If I filter on just this key in Power Query, every seems fine. I load it and look at it the the data tables, everything seems fine. Then I bring the columns into a Power BI table visual and suddenly 06RZ maps to other ColB values like NT, CV, etc.

 

Yesterday I was working on solving this problem, then I copied the faulty table to a new tab and suddenly it seemed fine!! Then I went back to the old tab and that one was fine too! Then I proceeded to work more on it and suddenly they were all broken again. Sometimes, I'll recieve a "not enough memory to display" error, so perhaps it's a bug related to limited memory. I'll proceed with that idea and I have a couple more troubleshooting ideas but I've been troubleshooting this one issue for over 21 hours now. 

Microsoft
Microsoft

Re: Power Query Table != Power BI Table

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

 

Data Cache.PNG

SBuri Resolver II
Resolver II

Re: Power Query Table != Power BI Table

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? 

SBuri Resolver II
Resolver II

Re: Power Query Table != Power BI Table

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.

SBuri Resolver II
Resolver II

Re: Power Query Table != Power BI Table

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.

SBuri Resolver II
Resolver II

Re: 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.

View solution in original post

SBuri Resolver II
Resolver II

Re: Power Query Table != Power BI Table

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. 

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors