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

[Whiskeybar]

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:

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!

[Whiskeybar]

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.

[PowerN00b]

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

[Whiskeybar]

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]

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]

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

[Whiskeybar]

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