Duplicate rows when merging 2 snapshot tables

[KHSK]

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

[v-tsaipranay]

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.

[ajaybabuinturi · Accepted solution]

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.

[bhanu_gautam]

@KHSK , Create a new column as combination of ID and date in ID table and use it to join with Approval table