Downloads
Power Query MTable: rowsIntermediate

Table.Group

Groups rows by one or more key columns and calculates totals or counts for each group.

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

Returns: A table with one row for each group.

What it does

Table.Group is the M version of a GROUP BY or a summary pivot. It collapses many rows into one row for each key, such as an account, a customer or a month, and calculates a total or a count for that group. The result has only the key columns and the calculated columns, so bring any other figure you need in as an aggregate.

Syntax and arguments

Example

Sales lines from the sample database are summarized by product code, showing the total units sold and the number of order lines for each product.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Lines = Source{[Schema = "classicmodels", Item = "orderdetails"]}[Data],
    ByProduct = Table.Group(
        Lines,
        {"productCode"},
        {
            {"Units", each List.Sum([quantityOrdered]), type number},
            {"Lines", each Table.RowCount(_), Int64.Type}
        }
    )
in
    ByProduct

Each product code appears once in the result. Units is the sum of the quantity column in the group, and Lines counts the rows in the group.

Example: a second, different use

A general ledger is summarized by account and period, so each account has one net amount per period for a trial balance check.

let
    Source = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Amount", type number}}),
    Balances = Table.Group(Typed, {"Account", "Period"}, {{"Net", each List.Sum([Amount]), type number}})
in
    Balances

Things to watch

In business terms

Totals by account, customer, vendor or period are the core of most finance schedules. Table.Group produces them in the query, so the figure in the report can be traced back to the grouped rows.

Related functions

All Power Query M functions · View as Markdown · M basics