Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Whiskeybar
Advocate I
Advocate I

Data in Power Query is different from data loaded into the model

Hi!

I have a scary problem, and can not understand why it happens. It is the first time I observe such behaviour in several years.

The Data I observe in Data model is not the same as in Power Query.

HEre is an example:

 

Unbenannt.PNG

Data source is a bunch of csv files, where I combine them and do some transformation - plus a tiny sql query. Nothing complicated! How can it be, that I see two rows in Power Query and three in data model? 

 

Moreover, when you refresh my data, everything shuffles again - and the errors are in different places than before. That gives me really creeps..

 

 

Has anyone had such a problem before?

 

Thanks!

 

6 REPLIES 6
Whiskeybar
Advocate I
Advocate I

Just in case it an help someone - it looks like the reason was not in joins, but in one of the further transformation I made. These were  Custom Columns. When I removed these custom columns , the data started loaded normally. I am still trying to figure out what it has been. I actually did not do anything illegal, these were column with plain if/then rules. And creating such columns should not have changed the data in other columns on load to data model! I made these custom columns in DAX instead.

 

Do you know what – within the Custom Columns – caused the problems?

 

Whiskeybar
Advocate I
Advocate I

So, I have tried following things:

 

1) getting rid of null-values on merging columns (should have thought about it) - but nothing happened, still these creepy discrepancies.

2) getting rid of full outer join (hate it, because then I had to do more transformation tables to have everything I need in one) - but it did not help me neither

 

I am out of possible options, help! 

Whiskeybar
Advocate I
Advocate I

I do several merges on my data - one of which is full outer join. I suspect that it might be an issue - but nevertheless, I think Power Query is supposed to show the same data we see in the Data model.

I will try to avoid Full outer- hopefully it will solve the issue.

I also wrote an R script to export the Power Query table to Excel. In the Excel, data is identical to what I see in Power Query. So apparently, there is some problem on loading into Data Model.

amitchandak
Super User
Super User

@Whiskeybar , have you shared only one screenshot. Seems like there should be one more.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Yes, I have edited my post again. Somehow only one image uploaded. I had to recreate my issue and updated my post.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.