Table.Group Function in Power Query M (6 Easy Examples)

To summarize a table by category in Power Query, you group its rows and add aggregate columns in one step. The Table.Group function does exactly that.

It collapses rows that share the same key into a single row per group, then builds the totals, counts, or other measures you ask for.

Table of Contents

Syntax of Table.Group Function

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table

where each argument controls one part of the grouping.

table (required, table): The source table you want to group.
key (required, any): The column to group by. Pass one column name as text, or a list of names like {"Category","Channel"} to group by several columns.
aggregatedColumns (required, list): A list of the new columns to create. Each entry is itself a list shaped as {"NewName", each <aggregation over _>, optional type}. The _ refers to the sub-table of rows in the current group.
groupKind (optional, nullable number): Either GroupKind.Global (the default) or GroupKind.Local. Global groups matching rows anywhere in the table. Local groups only contiguous runs of matching rows.
comparer (optional, nullable function): A function that controls how keys are compared, for example to make grouping case-insensitive.

Returns: a table with one row per group. The key column(s) come first, followed by the aggregate columns you defined.

Every distinct key value becomes one output row that carries its own aggregates.

Example 1: Count the rows in each group

Start by counting how many orders fall under each category.

Here is the starting data:

Category	Channel	Amount
Coffee	Dine-in	40
Tea	Takeaway	20
Coffee	Takeaway	60
Pastry	Dine-in	30
Tea	Dine-in	20
Coffee	Dine-in	50
Pastry	Takeaway	10

Group by Category and add a Count column using Table.RowCount(_).

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Grouped = Table.Group(Source, "Category", {{"Count", each Table.RowCount(_), Int64.Type}})
in
    Grouped

The result produces:

Category	Count
Coffee	3
Tea	2
Pastry	2

Each group’s rows are passed to the aggregation as _, so Table.RowCount(_) returns the number of rows in that group. Categories appear in the order they were first seen.

Example 2: Sum a column per group

This time, total the Amount for each category instead of counting rows.

Using the same orders table, group by Category and sum [Amount].

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Grouped = Table.Group(Source, "Category", {{"Total", each List.Sum([Amount]), type number}})
in
    Grouped

The result produces:

Category	Total
Coffee	150
Tea	40
Pastry	40

Inside the aggregation, [Amount] is the Amount column of the current group’s sub-table. List.Sum adds those values, so Coffee returns 40 + 60 + 50 = 150.

Example 3: Build several aggregates in one call

You can define more than one aggregate column at once by adding more entries to the list.

Using the same orders table, group by Category and create a count, a sum, and an average together.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Grouped = Table.Group(Source, "Category", {{"Orders", each Table.RowCount(_), Int64.Type}, {"Total", each List.Sum([Amount]), type number}, {"Average", each List.Average([Amount]), type number}})
in
    Grouped

The result produces:

Category	Orders	Total	Average
Coffee	3	150	50
Tea	2	40	20
Pastry	2	40	20

The output columns follow the order you wrote them in: Orders, then Total, then Average. Each one runs its own function over the same group.

Example 4: Group by two key columns

Pass a list of column names to group by more than one field at a time.

Using the same orders table, group by both Category and Channel.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Grouped = Table.Group(Source, {"Category", "Channel"}, {{"Orders", each Table.RowCount(_), Int64.Type}, {"Total", each List.Sum([Amount]), type number}})
in
    Grouped

The result produces:

Category	Channel	Orders	Total
Coffee	Dine-in	2	90
Tea	Takeaway	1	20
Coffee	Takeaway	1	60
Pastry	Dine-in	1	30
Tea	Dine-in	1	20
Pastry	Takeaway	1	10

Now a group is each unique Category plus Channel pair. Both key columns appear first, in the order you listed them, before the aggregates.

Example 5: Find the highest and lowest value per group

Use List.Max and List.Min to pull the range of a column within each group.

Using the same orders table, group by Category and return the highest and lowest Amount.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Grouped = Table.Group(Source, "Category", {{"Highest", each List.Max([Amount]), type number}, {"Lowest", each List.Min([Amount]), type number}})
in
    Grouped

The result produces:

Category	Highest	Lowest
Coffee	60	40
Tea	20	20
Pastry	30	10

Both aggregates read the same [Amount] column of the group. Tea shows 20 for both because all its amounts are 20.

Example 6: Group only contiguous runs with GroupKind.Local

By default Table.Group groups matching rows anywhere in the table. Passing GroupKind.Local changes that. It groups only adjacent rows that share a key, so the table must already be sorted by that key.

This example uses a pre-sorted table where Coffee appears in two separate stretches:

Category	Amount
Coffee	40
Coffee	60
Tea	20
Tea	20
Coffee	50
Pastry	30
Pastry	10

Group by Category with GroupKind.Local as the fourth argument.

let
    Source = Excel.CurrentWorkbook(){[Name="OrdersSorted"]}[Content],
    Grouped = Table.Group(Source, "Category", {{"Total", each List.Sum([Amount]), type number}}, GroupKind.Local)
in
    Grouped

The result produces:

Category	Total
Coffee	100
Tea	40
Coffee	50
Pastry	40

Coffee shows up as two separate groups. The first run of two Coffee rows totals 100, and the later single Coffee row stays apart as 50. Local grouping never merges rows across the Tea rows that sit between them. The default Global grouping would have combined all three Coffee rows into one 150.

Things to keep in mind with Table.Group

Each aggregate is a three-part list: {"NewName", each <function of _>, optional type}. Forgetting the each keyword causes an error, since the function needs to run once per group.
The grouping key passes the current group’s rows in as _. Use Table.RowCount(_) to count, or read a column with [ColumnName] to feed List.Sum, List.Average, and similar functions.
Output columns are ordered key column(s) first, then your aggregates in the order you listed them.
Group rows appear in first-appearance order, matching when each key first shows up in the source.
GroupKind.Local groups only contiguous runs of the same key, so sort the table first. The default GroupKind.Global groups across the whole table regardless of position.
Add an explicit type such as Int64.Type or type number to each aggregate. Skip it and the new column lands as any.
To keep every original row inside each group, use an all-rows aggregate like each _. That stores the full sub-table in a column you can expand later.

Performance and query folding

Simple group-by aggregations fold well on a database source. A plain count or sum can translate into a GROUP BY query that runs on the server, which is fast even on large tables. Custom logic inside an each function usually breaks folding. When that happens, Power Query pulls all rows into memory and groups them locally, which is slower against big sources.

Common questions about Table.Group

Is Table.Group the same as a PivotTable?

They overlap but are not identical. A PivotTable is an interactive Excel layout you reshape on the sheet. Table.Group is a query step that produces a fixed, refreshable table you can keep transforming downstream.

How do I keep all the original rows in each group?

Use an all-rows aggregate. Add a column shaped as {"Rows", each _, type table}, which stores each group’s full sub-table. You can then expand that column later to bring the detail rows back.

List of All Power Query Functions

Related Power Query Functions / Articles: