Predict row counts: one-to-many and many-to-many merges
Know in advance how many rows a merge should produce and check it.
~10 min read · advanced · Module 12: Merging (joining) tables
By Tahir Nas · Examples run in our simulator · Updated
Before this: Merge queries: choosing a join kind, Check key quality before you rely on it
A left outer merge followed by an expand returns, for each left row, as many rows as it has matches, or one row if it has none. Write that down and you can predict the row count before running anything.
| Lookup side | Name | Rows after expanding a left outer merge |
|---|---|---|
| Key unique | Many-to-one | Exactly the left table's row count |
| Key repeats | One-to-many / many-to-many | Left rows × their matches: more rows than you started with |
Count matches before expanding
The nested table column holds each row's matches, so its row count is the number of rows that row will become. Add it as a column and you can see exactly where growth will come from:
let
Orders = #table({"OrderID", "Product"}, {{"A-1", "Kite"}, {"A-2", "Reel"}, {"A-3", "Line"}}),
Suppliers = #table({"Product", "Supplier"}, {{"Kite", "Skyward"}, {"Reel", "Castwell"}, {"Reel", "Riverline"}}),
Merged = Table.NestedJoin(Orders, {"Product"}, Suppliers, {"Product"}, "S", JoinKind.LeftOuter),
Matches = Table.AddColumn(Merged, "Matches", each Table.RowCount([S]), Int64.Type)
in
Table.RemoveColumns(Matches, {"S"})Result
| OrderID | Product | Matches |
|---|---|---|
| A-1 | Kite | 1 |
| A-2 | Reel | 2 |
| A-3 | Line | 0 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Prediction: 1 + 2 + 1 (no match still gives one row) = 4 rows after expanding, one more than the three orders. Reel has two suppliers, so order A-2 will appear twice.
let
Orders = #table({"OrderID", "Product"}, {{"A-1", "Kite"}, {"A-2", "Reel"}, {"A-3", "Line"}}),
Suppliers = #table({"Product", "Supplier"}, {{"Kite", "Skyward"}, {"Reel", "Castwell"}, {"Reel", "Riverline"}}),
Merged = Table.NestedJoin(Orders, {"Product"}, Suppliers, {"Product"}, "S", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "S", {"Supplier"})
in
ExpandedResult
| OrderID | Product | Supplier |
|---|---|---|
| A-1 | Kite | Skyward |
| A-2 | Reel | Castwell |
| A-2 | Reel | Riverline |
| A-3 | Line | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Is the growth right?
Sometimes more rows is the correct answer: listing every supplier who can fulfil each order is a one-to-many question. But if you then sum order amounts, A-2 counts twice. Decide which question the output answers. If you want one row per order, either pick one supplier per product first (a survivor rule), or don't expand at all and aggregate the nested table instead (next lesson).
A refresh-time check
For a lookup that must not multiply rows, compare counts every refresh and stop if they differ: if Table.RowCount(Expanded) <> Table.RowCount(Orders) then error "Lookup key isn't unique" else Expanded. A duplicate key added to the supplier list next month is then caught on the day it appears.
Where to find this in Excel
- Excel for Windows
- Home > Merge Queries
- What you see
- After you pick the key columns, the dialog shows an estimated number of matches before you select OK.
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
- Expanding a merge against a table with repeated keys and summing the duplicated amounts.
- Assuming no-match rows disappear in a left outer merge; they stay as one row with nulls.
- Removing duplicates after the merge, which hides the duplicate key instead of fixing it.
Excel, Power BI and the simulator
- The Merge dialog in Excel and Power BI Desktop reports 'The selection matches N of M rows from the first table'. That counts matched left rows, not output rows, so it won't warn you about duplicates in the lookup.
- In a Power BI model, the equivalent problem appears as a many-to-many relationship warning.
- The simulator's nested table column can be inspected with Table.RowCount exactly as shown.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Merging tables quiz