Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Power Query Merge Function

  • 1.  Power Query Merge Function

    Bronze Contributor
    Posted 9 days ago
    Good morning,

    I am getting a strange effect when I try to do a merge using a left outer join.

    I have my facts table and I am trying to look up certain descriptives from a certain dimensions table using a left outer join in power query.

    Before I do the merge, my numbers are ok, but after I do the merge, my numbers seem to be multiplied .

    I am trying to merge on a text column with which is a unique account code in the dimensions table.

    What could I be missing that produces this sort of multiplier effect. I dont want the figure in the facts table to be multiplied

    Thank you for any assistance.

    Regards

    Herbert

    ------------------------------
    Herbert Chitate
    Finance Manager
    ------------------------------


  • 2.  RE: Power Query Merge Function

    Posted 8 days ago
    1. Make sure you have all unique keys in your dimension table. a simple Distinct Count should confirm that it matches the rows in your dimension table.
    2. Make Fact table, your left table, then I would do a Merge in Power Query, "Merge Queries as New" .. to keep the old queries for reference for now.


    You can see in this demo, that the top table has alot of data, but missing the day name, the bottom table has that 1 column that I needed.
    Left outer join is what you want to do ..

    Question: why would you want to do this in Power Query? If you have a dimenstion table.. just bring it into the model and join on the keys.  You can create your pbi reports better with that type of model (Star Schema) rather than trying to put everything into 1 table.

    -Ka

    ------------------------------
    Ka Ly
    Clinical Informatics Program Manager
    ------------------------------