Forum Discussion
Inconsistent Data After Refresh - Merge Query Issue (?)
Hi all,
I encounter a very strange issue/behavior in PBI, which I suspect has something to do with the way merge transformation is used in PowerQuery.
Basically, I extract data from SQL Server database to PBI tables/queries. There are 5 queries in total (1 for Date, 3 "dimensions", and 1 "fact").
The source tables do not have obvious key field in them, I concatenate several fields to make up a key field.
Also, I added Index to the table/query in PowerQuery (via Add Column > Index Column).
I did this for the 3 "dimension" tables.
Then, for the "fact" table, I performed Merged Queries transformations (LEFT Outer with no fuzzy matching) with the 3 "dimension" tables based on the combination of fields which form the key fields.
From this transformation, I can then obtain the Index field (act like a surrogate key now) from each of the "dimension" tables, so that it replicates a proper star-schema / fact-dimension data model, which I believe is the best practice.
It was all working fine, until I performed data refresh.
Every time I refresh the data, even though the souce has not changed, the data in Power BI desktop keeps changing.
Looking at the Data tab, it seems that the Index keys in the fact table are out of whack, i.e. they dont correspond to the correct Index field value in the dimension tables.
This behaviour is also observed when I refresh data in Power BI Report Server and Power BI Service.
One thing that seems to "fix" it is to disable parallel loading in Power BI Desktop (File >Options and Settings > Options > Current File > Data Load > Parallel loading of tables > untick Enable parallel loading of tables).
It makes the data refresh/loading slow, but it seems that the resulting data does not get out of whack after it completes.
This only applies on Power BI Desktop though, i.e. even if I disable parallel loading in PBI Desktop, once it is published to PBIRS or PBI Service, the issue is still there.
Has anyone experienced this before? Any ideas on what to do or try here?
Is this a (known) bug? Or, perhaps I "misuse" Merge Queries transformation (if it is indeed the cause) ?
Thanks in advance.
14 Replies
Hi Anonymous ,
is my understanding correct that you're extracting the index-fields for the fact tables from the merge withe the Dim-Tables?
Then please add a buffer-step as the last step in your Dim-Tables:Table.Buffer(YourLastStepNameSoFar)
This should solve the problem.
Thanks ImkeF .
Yes, I am extracting the index fields from the dimension tables via Merge Queries transformation in the fact table, so that the fact table has the index fields from the dimension tables and act like surrogate keys in the fact table.
Is there anything wrong with this approach?
I added the Table.Buffer as you suggested. However, it seems to only work in Power BI Desktop, i.e. I can re-enable the parallel loading again and after refresh in PBI Desktop, the data does not get out of whack.
However, when I republish to PBI Service and refresh the data there, the data still gets out of whack after the refresh 😞
What does Table.Buffer actually do by the way? And why do you think I need to add it in the PowerQuery in this case?
Hi Anonymous ,
this sounds like a bug to me. I'd recommend to report it as such: https://community.powerbi.com/t5/Issues/idb-p/Issues
Table.Buffer should make the whole table immutable: https://bengribaudo.com/blog/2019/12/10/4778/power-query-m-primer-part12-tables-table-think-i
Although adding an index-column should do so as well, so it's strange already, that a buffer is needed for the dim table.
What you can try is to buffer the fact-table as well - just as an idea to solve the current problem.
But in theory it should work without...
I think I have found a solution by the power of SQL query. Instead of adding an index column in PowerQuery, after getting the data, I'm using ROW_NUMBER in my database SQL:
SELECT ROW_NUMBER() OVER( ORDER BY Column1, Column2) id_Num, Column1, Column2 [...]
So far my indexes have behaved on refreshing in the PowerBI Service. Hopefully it stays that way...!
Just to update, I've been using this approach (SQL ROW_NUMBER to add an index) since September and it is still working very well, without any problems, and is fast.
Hi. I am encountering exactly the same issue as described here. It was driving me crazy but good to read that I'm not the only one... I can try to remove all surrogate keys (index columns) and just link the Facts and Dims based on concatenated fields now, but that's not ideal and takes a lot of work. I am curious if anyone know whether this is a bug or not? And if anyone knows how to fix this? Maybe ImkeF , marcofalzone ?
