Merge two tables and avoid duplicates

[Anonymous]

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 note that there are duplicate names in the Exceptions table. Is it possible to do this all in Power Query? Any help is much appreciated!

Reference table:

Student Name

Lizui

Laufenburg

Tegalpapak

Ar Rabiyah

Bellegarde

Gangarampur

Luntas

Frei Paulo

Seedorf

Cosamaloapan de Carpio

Zagrodno

Exceptions table:

Student name	Reason
Zagrodno	sick
Luntas	sick
Gangarampur	sick
Luntas	sick
Frei Paulo	blank
Gangarampur	blank
Luntas	blank
Frei Paulo	late
Zagrodno	blank
Seedorf	blank
Gangarampur	late

[Anonymous]

[ImkeF]

Hi @Anonymous ,
you can expand the "Reason"-column from the merged table.
But you have to decide how to deal with multiple different entries for one student: Do you only want to keep the first/last or even concatenate them?

[ImkeF]

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

[Anonymous]

@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?

[Anonymous · Accepted solution]

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"