Table.Group
TableGroups rows by key columns and applies aggregation functions to produce summary results.
Syntax
Table.Group(table as table, key as any, aggregatedColumns as any, optional groupKind as nullable number, optional comparer as nullable function) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The input table to group. |
key | any | Yes | A column name or list of column names to group by. |
aggregatedColumns | any | Yes | A list of {newColumnName, aggregationFunction} pairs defining the aggregations. |
groupKind | number | No | GroupKind.Global (default) or GroupKind.Local for contiguous grouping. |
comparer | function | No | An optional comparer function for key comparison. |
Return Value
table — A table with one row per unique key value, with additional columns for aggregated results.
Remarks
Table.Group groups rows that share the same values in the specified key column(s), then applies aggregation functions to produce summary columns. Common aggregation patterns include List.Count for counting rows, List.Sum for summing values, and List.Average for averages.
The aggregatedColumns parameter takes a list of lists, where each inner list contains the new column name and a function that receives the grouped sub-table.
If your data is already sorted and groups are contiguous (all rows for each key value appear together), use GroupKind.Local as the fourth argument for better performance. Otherwise, the default GroupKind.Global scans the entire table.
If you sort a table before grouping, wrap the sorted table in Table.Buffer first. Without buffering, Table.Group does not guarantee that the row order from the preceding sort is preserved, which can lead to unexpected results.
When using the "All Rows" aggregation pattern (each _), always include a type annotation as the third element in the aggregation list (e.g., {"AllRows", each _, type table}) to improve performance and readability.
Examples
Example 1: Count rows by group
Table.Group(Sales, "Region", {{"OrderCount", each Table.RowCount(_), Int64.Type}})Region | OrderCount | |
|---|---|---|
| 1 | East | 3 |
| 2 | West | 3 |
| 3 | North | 2 |
Example 2: Sum values by group
Table.Group(Sales, "Category", {{"TotalQuantity", each List.Sum([Quantity]), type number}})Category | TotalQuantity | |
|---|---|---|
| 1 | Widgets | 28 |
| 2 | Gadgets | 6 |
| 3 | Misc | 1 |
Example 3: Multiple aggregations
Table.Group(
Sales,
"Region",
{
{"OrderCount", each Table.RowCount(_), Int64.Type},
{"AvgPrice", each List.Average([UnitPrice]), type number}
}
)Region | OrderCount | AvgPrice | |
|---|---|---|---|
| 1 | East | 3 | 53.33 |
| 2 | West | 3 | 41.67 |
| 3 | North | 2 | 45 |