Forum Discussion
Advocate IDuplicate 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 if someone can help me get the desired output as mentioned in the To Be table below.
| ID Table | Table after merging-AS IS | |||||||||||
| ID | SnapshotDate | Region | Merging ID & Approval tables on ID column as Left Outer Join | ID | SnapshotDate | Region | Status_AT | Approved On_AT | SnapshotDate_AT | |||
| 1 | 6/6/2025 | A | 1 | 6/6/2025 | A | Approved | 6/7/2025 | 6/13/2025 | ||||
| 2 | 6/6/2025 | B | 1 | 6/13/2025 | A | Approved | 6/7/2025 | 6/13/2025 | ||||
| 3 | 6/6/2025 | C | 2 | 6/6/2025 | B | Approved | 6/7/2025 | 6/13/2025 | ||||
| 1 | 6/13/2025 | A | 2 | 6/13/2025 | B | Approved | 6/7/2025 | 6/13/2025 | ||||
| 2 | 6/13/2025 | B | 3 | 6/6/2025 | C | Approved | 6/7/2025 | 6/13/2025 | ||||
| 3 | 6/13/2025 | C | 3 | 6/13/2025 | C | Approved | 6/7/2025 | 6/13/2025 | ||||
| Approval Table | Table after merging-To Be | |||||||||||
| ID | Status_AT | Approved On_AT | SnapshotDate_AT | ID | SnapshotDate | Region | Status_AT | Approved On_AT | SnapshotDate_AT | |||
| 1 | Approved | 6/7/2025 | 6/13/2025 | 1 | 6/6/2025 | A | null | null | null | |||
| 2 | Approved | 6/7/2025 | 6/13/2025 | 2 | 6/6/2025 | B | null | null | null | |||
| 3 | Approved | 6/8/2025 | 6/13/2025 | 3 | 6/6/2025 | C | null | null | null | |||
| 1 | 6/13/2025 | A | Approved | 6/7/2025 | 6/13/2025 | |||||||
| 2 | 6/13/2025 | B | Approved | 6/7/2025 | 6/13/2025 | |||||||
| 3 | 6/13/2025 | C | Approved | 6/7/2025 | 6/13/2025 | |||||||
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.
3 Replies
- bhanu_gautam
Super User
KHSK , Create a new column as combination of ID and date in ID table and use it to join with Approval table
- ajaybabuinturi
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. 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.
