Join kind
Which rows a merge keeps: left outer, inner, left anti, right anti, full outer or right outer.
Left outer keeps every row of the first table, inner keeps only matches, anti joins keep only non-matches, and full outer keeps everything from both. Choosing the kind is a business decision: whether an order without a customer should appear in the report or be flagged.
In the Power Query editor
The Join Kind list at the bottom of the Merge dialog.
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.