Index Column and Merge Issue

[Anonymous]

Hello everyone,

I have been using Power Query to transform a single table held in SQL into a dimensional model. It works as follows:

1. Single table in SQL server which holds the entire dataset. ~ 2.5 million rows. 

2. One query in Power Query which connects to this table, performs steps to remove unnecessary columns, de-duplicate the data and add an index column. This forms a dimension.

3. One query in Power Query which connects to this table, does a merge to the newly created dimension table on the fields held within the dimension to go and get the index columm. The merge makes a perfect match each time. 

4. In the model view the two tables are joined by the index column in the relationship. 

When refreshing the dataset the numbers being shown keep changing; sometimes they are right, sometimes they are wrong. It appears the index column is not being passed correctly over from the dimension to the fact in the merge when it refreshes. In Power Query everything looks fine. Interestingly It is only ever the index that is wrong. If I merge back any of the dimensional columns back into the fact, and keep the original columns insde the fact they all match. It is only ever the index that is incorrect.

Refresh 1:

 

Refresh 2:

Refresh 3:

Has anyone ever encountered this before and any tips for why it is happening / how to stop it happening? The above method seems pretty standard way of creating a dimensional model from a single table so unsure why this would be happening. 

Any help is much appreicated.

Thanks,

Alex

[Anonymous · Accepted solution]

I found adding an explicit sort step in Power Query after doing the de-duplication fixed the issue. 

[jennratten]

Hello - can you please post your m-queries?