Composite key
A key made of several columns that together identify a row, such as Store + Product for a price list.
When one column alone isn't unique, matching on several columns together gives a unique key. In the Merge dialog, select the key columns in the same order in both tables. Cleaning every part of the key (trim, case, type) matters as much as with a single key.
In the Power Query editor
Ctrl+click several columns in each table of the Merge dialog; the numbers show which columns pair up.
Learn more
Practise it
- advanced~10 minPro
Look up prices by shop and product together (farm shops)
Merge Sales with Prices on both Store and Product, then calculate line revenue.
- 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.