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


   table as table,
   key as any,
   aggregatedColumns as list,
   optional groupKind as nullable number,
   optional comparer as nullable function,
) as table
TableThis is the main table you’re grouping.
KeyRefers to the column(s) to group by, such as a single column “col1” or a list {“col1”, “col2”, “col3”}.
AggregatedColumnsProvide an aggregation using a function that supports lists, in the format:
{ “ColumnName”, each Fx(_) , type text }
GroupKindOptionalGroup 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.
ComparerOptionalAllows you to define how to compare values when grouping. E.g. ignoring capitals or applying a function to that determines the grouping.


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.


Let’s see Table.Group function in action. Imagine you’re working with the following table in a step titled ‘Source‘.

Table.Group dataset in Power Query M

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 with GroupKind Global in Power Query
= Table.Group( 
    { "CustomerID" },
    { "Total", each List.Sum( _[Price] ), Int64.Type }

/*   ---------------------------------------------------
     -- which is identical to --
     --------------------------------------------------- */

= Table.Group( 
    { "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 with GroupKind Local in Power Query
    { "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.

  // Define a source table with records of CustomerID and Price.
  Source = 
        [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 = 
        { "CustomerID" }, 
        {"Total", each List.Sum(_[Price]), Int64.Type}, 
        // GroupKind.Global ensures that grouping is done globally, considering all records at once.
  // Group the records by CustomerID and calculate the local sum of Price for each group.
  GroupKindLocal = 
        { "CustomerID" }, 
        {"Total", each List.Sum(_[Price]), Int64.Type}, 
        // GroupKind.Local ensures that grouping is done locally, considering continuous series of values.
// End the M code and output the table with local grouping applied

Learn more about Table.Group in the following articles:

Other functions related to Table.Group are:

BI Gorilla Blog

Last update: August 28, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation:
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.