Table.Group
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Groups rows by key columns and aggregates each group.
When to use it
Use it to summarise rows per key (totals, counts, min and max), or with All Rows to keep each group's table for further work such as top N per group. It replaces most pivot-table style summaries.
Syntax
Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as tableParameters
- key
- Column name(s) to group by.
- aggregatedColumns
- A list of {name, function, type}; the function receives the group's table.
Returns
One row per distinct key with the aggregations.
Example
Table.Group(#table({"Region", "Product", "Qty"}, {{"North", "Kite", 2}, {"South", "Reel", 5}, {"North", "Reel", 1}, {"West", "Kite", 4}}), {"Product"}, {{"Total", each List.Sum([Qty]), type number}, {"Rows", each Table.RowCount(_), Int64.Type}})Result
| Product | Total | Rows |
|---|---|---|
| Kite | 6 | 2 |
| Reel | 6 | 2 |
Example: count and sum per region
Each aggregation receives the group's rows as a table, so Table.RowCount(_) counts them.
Table.Group(#table({"Region", "Rep", "Amount"}, {{"North", "Ana", 120}, {"South", "Bo", 80}, {"North", "Cy", 200}, {"South", "Ana", 50}}), {"Region"}, {{"Orders", each Table.RowCount(_), Int64.Type}, {"Total", each List.Sum([Amount]), type number}})Result
| Region | Orders | Total |
|---|---|---|
| North | 2 | 320 |
| South | 2 | 130 |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Inside the aggregation, [Qty] is the group's Qty column as a list.
- Sort afterwards if order matters.
Common mistakes
- Grouping on a key with stray spaces, which splits one group in two.
- Forgetting that rows with a null key form their own group.
The matching command in Excel
- Excel for Windows
- Transform > Group By (also on the Home tab)
- What you see
- The Group By dialog sets the key columns and one new column per aggregation.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Grouping or summarizing rows (Microsoft Learn).
Exercises that use Table.Group
- beginner~10 minFree
Clean and summarize a sales export
Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.
- beginner~8 minFree
Reorder steps so cleaning happens before grouping (kite shop)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- intermediate~7 minFree
Fix a step that silently skips the cleaning step (kite shop)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.
- advanced~10 minPro
Keep each person's most recent signup (newsletter)
For every email keep only the row with the latest Signed date, whatever the row order.
- intermediate~8 minPro
Summarise orders by region (kite shop)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Count distinct customers per product (kite shop)
For each product, count how many different customers ordered it.
Learn more
- Group by with several aggregations lesson
- Group By recipes: count if, concatenate, first and last, top value guide
- Top N per group in Power Query, with ties handled guide
- Your first Power Query in Excel, from table to refresh guide
Related functions
Official documentation: Table.Group on Microsoft Learn.