Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 Serve...
Need Help
ImkeF's avatar
ImkeF
Community Champion
6 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?

    • ImkeF's avatar
      ImkeF
      Community Champion
      6 years ago

      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...

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        ImkeF , I did some brief testing by adding Table.Buffer on the fact table and remove the Table.Buffer in the dimension tables.

        In Power BI Desktop, it seems to work fine, i.e. data does not get out of whack after refresh there.

        However, the issue still persists when I publish to PBI Service and refresh there.

         

        Basically, what I am trying to do is following this approach of modelling.

        https://radacad.com/build-your-first-star-schema-model-in-action-power-bi-modeling-basics

         

        Since the issue seems to be caused by PowerBI/PowerQuery incorrectly retrieves the Index field values after Merged Queries transformation in the fact table, I removed all the relationships which use these key/index fields.

        Instead, I now use the concatenation of multiple fields to form the keys on each tables and join them in the Relationship.

        By doing this, no issue with data getting out of whack - PBI Desktop and PBI Service data refresh seem fine.

         

        Really disappointing something basics like this cause lots of issues and wasted lots of time, especially because I know some PBI experts like Reza (as above) and others promote this approach of data modelling..