Table.AggregateTableColumn
Sums, counts or otherwise sums up each row's nested table into new columns, one per aggregation.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.AggregateTableColumn(table as table, column as text, aggregations as list) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table with a column of nested tables. |
column | text | The column that holds the nested tables. |
aggregations | list | A list of {column, function, new name}: the nested tables' column to read, a function of a list such as List.Sum or List.Count, and the name of the column it makes. |
Returns
table. The table with the nested column replaced by the aggregations' columns.
Remarks
- This is Transform data's Aggregate choice on a merged column, after
Table.NestedJoin. - The new columns take the nested column's place.
Examples
Units and product count per order
let
Orders = #table({"Order", "Lines"}, {
{1, #table({"Product", "Units"}, {{"Chair", 4}, {"Lamp", 2}})},
{2, #table({"Product", "Units"}, {{"Desk", 1}})}})
in
Table.AggregateTableColumn(Orders, "Lines", {{"Units", List.Sum, "Units"}, {"Product", List.Count, "Products"}})| Order | Units | Products |
|---|---|---|
| 1 | 6 | 2 |
| 2 | 1 | 1 |