Table.Group - Table Function

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. Uses Comparer Functions to determine how values are equated during grouping. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

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.

Ignore or Respect The Order of Values

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 with GroupKind Global in Power Query

= 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 with GroupKind Local in Power Query

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
  GroupKindLocal

Comparer Functions

When comparing values, you can make use of comparer functions. By default the function creates groups in a case sensitive way using Comparer.Ordinal. However, in case your groups should be formed in a case-insensitive way you can make use of Comparer.OrdinalIgnoreCase. Suppose you have the following table:

Table.Group dataset for summarizing with case sensitivity in power query

To group this value by the Name column you can use the following code:

Table.Group (
  Source, 
  { "Name" }, 
  { { "Count", each Table.RowCount ( _ ), Int64.Type } }
)

Since the Table.Group function uses the GroupKind.Global and Comparer.Ordinal enumerations by default, the following code is identical:

Table.Group (
  Source, 
  { "Name" }, 
  { { "Count", each Table.RowCount ( _ ), Int64.Type } }, 
  GroupKind.Global, 
  Comparer.Ordinal
)

The fifth argument, that specifies the comparer function instructs the function how to group values. Depending on whether you use Comparer.Ordinal or Comparer.OrdinalIgnoreCase, the following tables are the result:

Table.Group with and without case sensitive comparer function in power query

Specifying Comparer.Ordinal results in a case-sensitive comparison, where each value in the Name column is a unique group. When we change it to Comparer.OrdinalIgnoreCase, the function ignores capitalization differences and we end up with only three unique groups.

You can try this for yourself using:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKspMzlbSUTI0UIrViVYKgvLMwDyXxLy8SiDXCMJNSfTzA3ENTSGyjn5+kUCuBUSnp7M3kGMC5vhk5oAVGkF4nj5gUyBWpDjmgU0xhnBzIEqNgBpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Amount = _t]),
  #"Grouped rows" = Table.Group(Source, {"Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}, GroupKind.Global, Comparer.OrdinalIgnoreCase ),
  Custom = Table.Group(Source, {"Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}, GroupKind.Global, Comparer.Ordinal )
in
  Custom

Custom Comparer Function

Now let’s look at a scenario that can make use of a custom comparer function. In below table you can find three columns. The table is ordered by date, and whenever there is a contiguous date range, only the first row of the same date will show the date value:

Now suppose we want to group our values by date and show which people bought something on a particular date and at the same time return the total amount. A common way to do that is to use the Fill Down operation on the Date column. Yet, there’s also a way in which we can do that by using a custom comparer. Since we are using GroupKind.Local, we can make use of the simplified comparer below:

Table.Group (
  Source, 
  { "Date" }, 
  {
    { "Names", each Text.Combine ( [Name], ", " ), type nullable text }, 
    { "Total Amount", each List.Sum ( [Amount] ), type nullable number }
  }, 
  GroupKind.Local, 
  ( x, y ) => Number.From ( y[Date] is date )
)

The outcome of this operation is the following table:

Table.Group using a Custom Comparer Function in Power Query M

So what happend here? In the expression used, the fifth argument defines a custom comparer function. This function tests whether the underlying values are date values. The comparer function in argument 5 expects a number value of -1, 0 or 1, just like Comparer.Ordinal does. The example uses the Number.From function to turn true values into 1 and false values into 0.

To make the outcome of the custom function (x, y ) => Number.From( y is date ) more visual, below table shows a separate column with the outcome of this evaluation:

Table.Group Custom Comparer creates groups based on condition

The last column returns 1 every time a value is a date. In this dataset, that happens for row 1, 4, 6 and 10. In the context of the Table.Group function, whenever a row meets the required condition, and therefore returns 1, a new group is formed. That means the first group ranges from row 1-3, the second from row 4-5, the third from row 6-9 and the final group is only row 10.

To try this for yourself you can paste the following code into the advanced editor:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MAQiJR0ll8S8vEogbWigFKsTrQRkBWUmZ4MEzGACvqk5mcXFiUCWEUQMZoChMVDMMSWzpBIkaWgK15CZnJGYmgNkWaCoNzIACrll5ueBlJvAVPtk5oAdYAQTCMkHu8gI7iLfxKJMkBZjAxTjjEHuD8vMz0ktASkHGhgLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Amount = _t]),
  chType = Table.TransformColumnTypes(Source, {{"Amount", Int64.Type}, {"Date", type date}}),
  Grp = Table.Group(chType, 
"Date", 
{{"Names", each Text.Combine([Name], ", "), type nullable text}, 
{"Total Amount", each List.Sum([Amount]), type nullable number}}, 
GroupKind.Local,
(x, y ) => Number.From( y is date ) )
in
  Grp

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
Using Custom Comparers with Table.Group in Power Query
This article shows you how to use the Comparer Function in Table.Group. You’ll also learn how to create a custom comparer function for more precise data grouping. » Read more

Other functions related to Table.Group are:

Table.FuzzyGroup

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-group