<!-- https://learnsql.cpetoday.com/functions/m/table-group -->
# Table.Group

Language: Power Query M
Category: Table: rows
Syntax: Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table
Summary: Groups rows by one or more key columns and calculates totals or counts for each group.
Returns: A table with one row for each group.
Level: Intermediate
Related: Table.Distinct, Table.Sort, Table.RowCount, Table.Max

## 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
- **table**: the table to group. Required.
- **key**: a column name, or a list of column names in braces, to group by. Required.
- **aggregatedColumns**: a list. Each item is a name, a function that works on the group, and optionally a type. Inside the function, `_` is the group's rows. Required.
- **groupKind**: `GroupKind.Global` (the default) or `GroupKind.Local`. Local is faster only when the table is already sorted by the key.
- **comparer**: how to compare key values, such as `Comparer.OrdinalIgnoreCase` for names that differ only by capital letters. Optional.

## 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.

```powerquery
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.

```powerquery
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
- Only the key and aggregated columns come out. Any other column is gone, unless you add it as an aggregate.
- Use `Table.RowCount(_)` to count rows in a group. The `_` is the group's own table.
- The rows in the result do not come out in a guaranteed order. Add a Table.Sort after the group if the order matters.
- Blank key values may form their own group. Check for them.
- Query folding: a group with simple sums and counts usually folds to GROUP BY. Custom functions or a Table.Buffer before the group usually stop it. Check View Native Query.

## 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.
