Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Removing Duplicates from Merged Tables in Power Query

I am doing a reconciliation from two different data set to find variances but when I do a full outer join between the tables I get duplicates if one of the tables are more than one row for the merge ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Syndicate_Admin ,

     

    Please try:

    1. Add an index column to both tables;

    2.  Merge Queries --> Merge Queries as New --> Select Table A as the first table and Table B as the second table --> Select the Resource, Date, Hours, and Index columns in both tables --> Choose the Full Outer join type and click OK

    3. Expand the new merged column to include the desired columns from Table B.

    4. Rename the columns as needed.

     

    Result:

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Martin_D's avatar
Martin_D
Solution Sage
3 years ago

You cannot produce the result you want from a single merge step. If your requirements demand these empty cells then you need to clean up them afterwards. E.g., you could compare the first 3 columns with the previous row as described here, check whether a Table A row is a duplicate of the previous row, and add custom columns in your first-and-blank-rows format. Then finally remove the columns from table A from the merge result that you don't want to have.