Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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"
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?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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"