Expand or aggregate a merge result
Choose between expanding and aggregating nested merge results, and do both correctly.
~9 min read · intermediate · Module 12: Merging (joining) tables
By Tahir Nas · Examples run in our simulator · Updated
Before this: Predict row counts: one-to-many and many-to-many merges
Table.NestedJoin doesn't add columns directly. It adds one column holding a table of matches for each row. Expanding turns those tables into rows and columns. Aggregating summarises each table into a value, so the row count never changes.
Aggregate: one row per customer
let
Customers = #table({"Cust", "Name"}, {{"C1", "Ana"}, {"C2", "Ben"}, {"C3", "Cleo"}}),
Orders = #table({"OrderID", "Cust", "Amount"}, {{"A-1", "C1", 20}, {"A-2", "C1", 35}, {"A-3", "C2", 12}}),
Merged = Table.NestedJoin(Customers, {"Cust"}, Orders, {"Cust"}, "Orders", JoinKind.LeftOuter),
Counted = Table.AddColumn(Merged, "Order count", each Table.RowCount([Orders]), Int64.Type),
Totalled = Table.AddColumn(Counted, "Total", each List.Sum([Orders][Amount]), type number)
in
Table.RemoveColumns(Totalled, {"Orders"})Result
| Cust | Name | Order count | Total |
|---|---|---|---|
| C1 | Ana | 2 | 55 |
| C2 | Ben | 1 | 12 |
| C3 | Cleo | 0 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
[Orders][Amount] is the Amount column of this customer's nested table, as a list.Cleo has no orders: her count is 0 and her total is null, because List.Sum of an empty list is null. If the report should show 0, say so explicitly: List.Sum([Orders][Amount]) ?? 0, or a conditional.
Expand: one row per order
Expanding the same merge gives one row per order, plus one row with nulls for Cleo. That's the right shape for an order-level table with customer names attached, and the wrong one for a customer list.
| Question | Do this |
|---|---|
| Which customer placed each order? | Merge Orders → Customers and expand |
| How many orders and how much per customer? | Merge Customers → Orders and aggregate |
| Which customers have no orders? | Left anti join Customers → Orders |
The editor's Aggregate option
The expand button in Excel and Power BI offers an Aggregate mode, which writes Table.AggregateTableColumn. The simulator doesn't implement that function, so it says so rather than approximating it:
Table.AggregateTableColumn(
#table({"Cust", "Orders"}, {{"C1", #table({"Amount"}, {{20}, {35}})}}),
"Orders",
{{"Amount", List.Sum, "Total"}}
)Not run by the simulator
The function or constant 'Table.AggregateTableColumn' isn't available in the simulator's supported subset. Check the spelling, or see the compatibility page. This code is shown for real hosts only; see the simulator compatibility page.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The custom column approach above gives the same result in every host, and it's easier to read: each aggregation is a named step with a visible formula.
Where to find this in Excel
- Excel for Windows
- The expand icon on the merged column's header
- What you see
- A list of the right table's fields appears, with options to expand them into columns or aggregate them.
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 when the question needs one row per customer, then trying to deduplicate.
- Expecting List.Sum of no matches to be 0; it's null.
- Merging in the wrong direction (orders into customers instead of customers into orders) for the question being asked.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: the expand button on a nested table column has Expand and Aggregate options; Aggregate writes Table.AggregateTableColumn.
- Custom columns over the nested table, as shown here, work in every host.
- The simulator raises UnsupportedFeature for Table.AggregateTableColumn; use the custom-column pattern instead.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Merging tables quiz