Table.Group
Groups rows that share the values of key columns and works out a column per group, such as a total or a count.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table| Parameter | Type | Description |
|---|---|---|
table | table | The rows to group. |
key | any | The key column, or a list of key columns. |
aggregatedColumns | list | One or more {name, function, type}: the new column's name, a function of the group's rows as a table (each List.Sum([Units])), and optionally its type. |
groupKind | nullable number optional | GroupKind.Global (the default): all rows with the same key are one group; GroupKind.Local: only rows next to each other are. |
comparer | nullable function optional | How keys are compared, such as Comparer.OrdinalIgnoreCase. |
Returns
table. One row per group: the key columns, then the new columns.
Remarks
- Groups come in the order their key first appears.
- Inside the function,
_is the group's table:Table.RowCount(_)counts its rows,[Units]is its Units column as a list. - This is the step Group by writes in Transform data.
Examples
Units and lines per shop
let
Sales = #table({"Shop", "Product", "Units"}, {{"Oslo", "Chair", 4}, {"Bergen", "Desk", 1}, {"Oslo", "Lamp", 6}, {"Bergen", "Chair", 2}})
in
Table.Group(Sales, {"Shop"}, {
{"Units", each List.Sum([Units]), type number},
{"Lines", each Table.RowCount(_), Int64.Type}})| Shop | Units | Lines |
|---|---|---|
| Oslo | 10 | 2 |
| Bergen | 3 | 2 |
Runs of the same status, with GroupKind.Local
let
Days = #table({"Day", "Status"}, {{1, "Open"}, {2, "Open"}, {3, "Closed"}, {4, "Open"}})
in
Table.Group(Days, {"Status"}, {{"Days", each Table.RowCount(_)}}, GroupKind.Local)| Status | Days |
|---|---|
| Open | 2 |
| Closed | 1 |
| Open | 1 |
The fourth day starts a new group: it doesn't follow the other open days.
Keys in different case as one
let
Sales = #table({"Product", "Units"}, {{"Chair", 4}, {"chair", 2}, {"Desk", 1}})
in
Table.Group(Sales, {"Product"}, {{"Units", each List.Sum([Units])}}, GroupKind.Global, Comparer.OrdinalIgnoreCase)| Product | Units |
|---|---|
| Chair | 6 |
| Desk | 1 |
The group shows the key as its first row writes it.
Related functions
Table.FuzzyGroup, Table.AggregateTableColumn, Table.Distinct, List.Sum.