Data is different in PowerBI and Power Query

[redeyequal]

Hello,

While working with PowerBI I noticed that the numbers look strange when I looked into the numbers, I noticed that the numbers are being shown in PowerBI -> Tables are actually different what I was in Power Query, that can be confirmed, if I click on "Transform Data" and see the data in a particular column in Power Query by sorting in desc order, and then compare the numbers in the table in powerBI by also sorting in desc order, the numbers should be same but they are not. I don't seem to understand why it is happening, I tried to import the data again but the same problem. I actually got an error while writing a calculated measure for this column for int type, so while debugging I found this, I am not sure if they are connected. But I am sure the column in both of the tables have the "whole number" data type.

Would be very helpful, if someone can help.

Regards.

[MeirStroh]

It's possible the reason you're not seeing everything is cause in powerquery it only shows the first 1000 rows and in the table view in power bi it shows a different number of rows 

A way you can check this is in the power bi table view scroll to the last row and see which number tow it is and compare that number with the same row number in power query 

I hope this helps somewhat 

[redeyequal]

That is why I sorted in desc order, so even if I am not seeing all the rows, I should be able to compare atleast the top rows. Or do you mean power query sorts only the top 1000 rows and ignoring the other ones?

[MeirStroh]

sort in power bi in desc order and see the top rows number and also scroll down till the bottom to see how many rows are being displayed like by me I saw there were 465 I think and then sort power query in asc order and scroll down to 465 or whatever number you saw in power bi and see if those numbers match 

let me know what you see 

[redeyequal]

I have some 8000 rows, and both Power Query and PowerBI showing all the rows if I scroll down.

[MeirStroh]

Strange. Can it be that you're filtering any other columns or something like that?

[redeyequal]

No and yes its very strange. For now I have again started from the scratch and I loaded the file locally this time, so now it's working fine. So I can atleast go forward. But later I will again import it from sharepoint like before and not sure if will face the same problem.

[Anonymous]

Hi @redeyequal ,

It's really weird. Following the normal process, you will not encounter such a problem.

Are you applying the latest data from power query to Power BI Desktop? Sometimes the data is transformed in Power Query, but the desktop is still showing the old data because it has not been applied and loaded.

Best Regards,
Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MeirStroh]

Hope you figure it out

[rajendraongole1]

Hi @redeyequal  - Go to Transform Data → Applied Steps (Right Panel).Look for transformations that might be affecting the column (e.g., removing duplicates, type conversions, or rounding).If necessary, remove or modify those steps and reload the data.

Even though Power Query and Power BI show the column as "Whole Number," Power BI may still be treating the column differently (e.g., rounding, truncation, or implicit type conversion).Go to Power BI Model View and check the column data type in the table properties.If needed, explicitly set it to Whole Number (not Auto).

• Check the default aggregation (Sum, Average, Count, etc.).
• In Power BI Table View, go to the column and ensure it’s not aggregating.

Try these steps and let me know what you find!