Forum Discussion

KHSK's avatar
KHSK
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Duplicate rows when merging 2 snapshot tables

  Hello All,   I have 2 snapshot tables which I want to merge but I get duplicate rows when doing the merge on ID column when I select Left Outer Join in the Join Kind drop down. Appreciate ...
Need Help
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    Hi KHSK,

    You can merge these tables based on ID and SnapshotDate from "ID Table" and ID and SnapshotDate_AT from "Approval Table" using Left outer join. To select multiple columns press and hold ctrl button. Then expand the results.


    Result:

     

    Thanks,
    If you found this solution helpful, please consider giving it a Likeđź‘Ť and marking it as Accepted Solutionâś”. This helps improve visibility for others who may be encountering/facing same questions/issues.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

HI KHSK ,

Thank you for reaching out to the Microsoft Fabric Community forum. Based on your sample tables, the duplication is occurring because the merge is only based on ID, which causes each match from the Approval Table to duplicate across all ID rows.

 

As correctly pointed out by ajaybabuinturi  the best approach is to merge using both ID and SnapshotDate (from the ID table) and ID and SnapshotDate_AT (from the Approval table). This ensures that only matching snapshots are joined, eliminating duplicates.

Also, thank you bhanu_gautam  for suggesting the composite key idea that’s a good practice when working with historical or snapshot data.

Also, when performing the merge, hold Ctrl to select multiple columns from each table to use in the join.

I hope this reslove your issue, if you need any further assistance, feel free to reach out.

 

Thank you.