Forum Discussion
Merge two tables and avoid duplicates
Hello, I have the following two tables and i am trying to create a new table in Power Query - merge and keep only the rows in the Reference table which are not found in the Exceptions table. Please ...
let Source = Table.NestedJoin(reftab, {"Student Name"}, exctab, {"Student name"}, "exctab", JoinKind.LeftOuter), #"Lowercased Text" = Table.TransformColumns(Source,{{"exctab", each Text.Combine([Reason],", "), type text}}) in #"Lowercased Text"
Hi Anonymous ,
when merging tables, you can adjust the join type to keep only those rows which are NOT included in the other table. That's called a "Left Anti Join": Multiple JOINS Types for Beginners | Power Query and Power BI (2021) Tutorial - YouTube
ImkeF I got this result and it looks correct:
But i have two questions:
1. Is it safe to delete the new column to the right or is it important to keep it?
2. Is it possible to bring in the "Reason" column from the Exceptions table into the result table?
let Source = Table.NestedJoin(reftab, {"Student Name"}, exctab, {"Student name"}, "exctab", JoinKind.LeftOuter), #"Lowercased Text" = Table.TransformColumns(Source,{{"exctab", each Text.Combine([Reason],", "), type text}}) in #"Lowercased Text"