Anti join
A merge that returns rows with no match: left anti for rows only in the first table, right anti for rows only in the second.
Anti joins are the quickest data-quality check in Power Query: which orders have no customer, which products have no price, which accounts disappeared since last month. The result has the first table's columns only, so remove the empty nested column it adds.
In the Power Query editor
Left Anti or Right Anti in the Merge dialog's Join Kind list.
Learn more
Practise it
- intermediate~9 minPro
Look up each order's customer city (kite shop)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~8 minPro
Find orders from unknown customers (kite shop)
Use a left anti join to list orders whose customer isn't in the customer master.
- beginner~6 minPro
Keep only enrolments for courses that are still running (college)
Enrolments refer to course codes; the Courses table lists only courses still running. Keep matching enrolments with their title.
- advanced~14 minPro
Reconcile bank and ledger with a full outer join (charity)
Match bank lines to ledger entries by Ref and flag what's only in the bank, only in the ledger, or in both.