Table.Group is a Power Query M function that groups rows of a table by the key columns defined by key. The function returns a table with grouped rows and a record containing key columns and any aggregated columns.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Table.Group(
table as table,
key as any,
aggregatedColumns as list,
optional groupKind as nullable number,
optional comparer as nullable function,
) as table| Argument | Attribute | Description |
|---|---|---|
| Table | This is the main table you’re grouping. | |
| Key | Refers to the column(s) to group by, such as a single column “col1” or a list {“col1”, “col2”, “col3”}. | |
| AggregatedColumns | Provide an aggregation using a function that supports lists, in the format: { “ColumnName”, each Fx(_) , type text } | |
| GroupKind | Optional | Group Types determine how rows are grouped together. GroupKind.Global considers all rows when forming groups, whereas GroupKind.Local forms groups from consecutive sequences of rows with the same value. |
| Comparer | Optional | Allows you to define how to compare values when grouping. E.g. ignoring capitals or applying a function to that determines the grouping. |
Description
Groups the rows of table by the key columns defined by key. The key can either be a single column name, or a list of column names. For each group, a record is constructed containing the key columns (and their values), along with any aggregated columns specified by aggregatedColumns. Optionally, groupKind and comparer may also be specified.
If the data is already sorted by the key columns, then a groupKind of GroupKind.Local can be provided. This may improve the performance of grouping in certain cases, since all the rows with a given set of key values are assumed to be contiguous.
When passing a comparer, note that if it treats differing keys as equal, a row may be placed in a group whose keys differ from its own.
This function does not guarantee the ordering of the rows it returns.
Examples
Let’s see Table.Group function in action. Imagine you’re working with the following table in a step titled ‘Source‘.
Want to group this table by CustomerID and sum up those prices? Add an aggregate column, let’s call it ‘Total’. This ‘Total’ will sum up the prices like so: “each List.Sum( _[Price] )”.
= Table.Group(
Source,
{ "CustomerID" },
{ "Total", each List.Sum( _[Price] ), Int64.Type }
)
/* ---------------------------------------------------
-- which is identical to --
--------------------------------------------------- */
= Table.Group(
Source,
{ "CustomerID" },
{ "Total", each List.Sum( _[Price] ), Int64.Type },
GroupKind.Global // Specifies groupkind global
)Notice how we’ve included the optional GroupKind.Type in the 4th argument? That’s us telling Power Query how we want our data grouped. When this argument is unspecified Power Query defaults to GroupKind.Global, which means it will consider all rows when forming groups.
But what if we want to group data based on the sequence it appears in the input table, i.e., local grouping? You can specify GroupKind.Local instead.
Table.Group(
Source,
{ "CustomerID" },
{ "Total", each List.Sum( [Price] ) },
GroupKind.Local // Specifies groupkind local
)Want to try both methods out for yourself? Copy the code below, create a blank query, and paste it into Power Query’s advanced editor.
let
// Define a source table with records of CustomerID and Price.
Source =
Table.FromRecords(
{
[CustomerID = 1, Price = 2],
[CustomerID = 2, Price = 1],
[CustomerID = 2, Price = 2],
[CustomerID = 1, Price = 1],
[CustomerID = 2, Price = 2],
[CustomerID = 2, Price = 5]
},
// Specify the type for each column in the table
type table [CustomerID = Int64.Type, Price = Int64.Type]
),
// Group the records by CustomerID and calculate the global sum of Price for each group.
GroupKindGlobal =
Table.Group(
Source,
{ "CustomerID" },
{"Total", each List.Sum(_[Price]), Int64.Type},
// GroupKind.Global ensures that grouping is done globally, considering all records at once.
GroupKind.Global
),
// Group the records by CustomerID and calculate the local sum of Price for each group.
GroupKindLocal =
Table.Group(
Source,
{ "CustomerID" },
{"Total", each List.Sum(_[Price]), Int64.Type},
// GroupKind.Local ensures that grouping is done locally, considering continuous series of values.
GroupKind.Local
)
// End the M code and output the table with local grouping applied
in
GroupKindLocalRelated articles
Learn more about Table.Group in the following articles:
- Concatenate Text with Group By (Combine Rows) in Power Query
In this post you learn how to use group by to concatenate text values. It allows you to summarize the data in a comma separated list. » Read more - Grouping or Summarizing Rows in Power Query
Grouping rows in Power Query can help show your data in a summarized way. You group rows by one or more columns and perform aggregations. » Read more
Related functions
Other functions related to Table.Group are:
