Power QueryExercises

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
    Expanded

Result

StoreProductQtyPrice
LeedsKite219
YorkKite121
LeedsReel46

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.

Three sales became six rows: each matched two price rows.

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.