Merge on composite keys
Join tables whose rows are identified by a combination of columns.
~9 min read · intermediate · Module 12: Merging (joining) tables
By Tahir Nas · Examples run in our simulator · Updated
Before this: Merge queries: choosing a join kind
A price list where each store sets its own prices is identified by Store and Product together; neither column is unique alone. Joining sales to it needs both columns as the key.
Pass both columns, in the same order
let
Sales = #table({"Store", "Product", "Qty"}, {{"Leeds", "Kite", 2}, {"York", "Kite", 1}, {"Leeds", "Reel", 4}}),
Prices = #table({"Store", "Product", "Price"}, {{"Leeds", "Kite", 19}, {"York", "Kite", 21}, {"Leeds", "Reel", 6}, {"York", "Reel", 6.5}}),
Merged = Table.NestedJoin(Sales, {"Store", "Product"}, Prices, {"Store", "Product"}, "P", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "P", {"Price"})
in
ExpandedResult
| Store | Product | Qty | Price |
|---|---|---|---|
| Leeds | Kite | 2 | 19 |
| York | Kite | 1 | 21 |
| Leeds | Reel | 4 | 6 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The key lists pair up by position: the first column of one list matches the first of the other. Names don't have to be the same ({"Shop", "Item"} with {"Store", "Product"} works), but the order does. In the merge dialog, you Ctrl-click the key columns in the same order in both tables; the small numbers on the headers show the pairing.
What a missing key column does
Merge on Product alone and each sale matches every store's price for that product. The row count grows and prices are wrong:
let
Sales = #table({"Store", "Product", "Qty"}, {{"Leeds", "Kite", 2}, {"York", "Kite", 1}, {"Leeds", "Reel", 4}}),
Prices = #table({"Store", "Product", "Price"}, {{"Leeds", "Kite", 19}, {"York", "Kite", 21}, {"Leeds", "Reel", 6}, {"York", "Reel", 6.5}}),
Merged = Table.NestedJoin(Sales, {"Product"}, Prices, {"Product"}, "P", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "P", {"Store", "Price"}, {"PriceStore", "Price"})
in
Table.RowCount(Expanded)Result
6
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Check the lookup key is unique
Before merging, confirm the combination is unique in the lookup table: Table.IsDistinct(Prices, {"Store", "Product"}) should be true. If it isn't, two prices exist for the same store and product, and you need a rule to choose one (for example the latest effective date) before the merge.
Where to find this in Excel
- Excel for Windows
- Home > Merge Queries, then Ctrl+select the key columns in each table
- What you see
- Small numbers on the headers show the pairing order, which must be the same in both tables.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Merge queries overview (Microsoft Learn).
Common mistakes
- Merging on one column of a composite key and multiplying rows.
- Selecting the key columns in a different order in the two tables.
- Building a concatenated key without a delimiter instead of merging on several columns.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: in the Merge dialog, Ctrl-click several columns in each table; numbers 1, 2… on the headers show which columns pair up.
- In Power BI models, relationships use a single column, which is why modellers sometimes build combined keys; inside Power Query, merge on the columns directly.
- The simulator supports multi-column keys in Table.NestedJoin and Table.Join.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Look up prices by shop and product together (farm shops)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- 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.
- advanced~10 minPro
Look up prices by shop and product together (vending)
Merge Sales with Prices on both Store and Product, then calculate line revenue.