Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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:

 

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!

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      PowerN00b
      New Member
      2 years ago

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

       

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!