Left outer join
A merge that keeps every row of the first table and adds matches from the second, with null where there's no match. The usual choice for lookups.
Rows of the first table are never lost, so totals based on it stay correct. Rows without a match get null in the expanded columns, which makes missing lookups easy to spot by filtering for null. If the lookup has duplicate keys, the first table's rows can multiply.
In the Power Query editor
Left Outer is the default in the Merge dialog; it's JoinKind.LeftOuter in M.
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.