Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 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 nameReason
Zagrodnosick
Luntassick
Gangarampursick
Luntassick
Frei Pauloblank
Gangarampurblank
Luntasblank
Frei Paulolate
Zagrodnoblank
Seedorfblank
Gangarampurlate
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago
    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"

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago
        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"

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    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?