Table.Group Function in Power Query

The Table.Group function in Power Query is used to grouping rows in a table based on specified columns and applying aggregate operations to the grouped data. It is similar to the GROUP BY clause in SQL.

Syntax

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

The function has the following parameters:

table: The table you want to group.
key: The column(s) we want to group by. This can be:

A single column name (as text, e.g., "Category").
A list of column names (e.g., {"Category", "Region"}).
A function that dynamically determines the grouping key.

aggregatedColumns: A list of aggregation operations to apply to the grouped data. Each item in the list is a list with:

Name: The name of the new column for the aggregated result.
Operation: The aggregation function (e.g., List.Sum, List.Average, List.Count, etc.). and the column to which the aggregation is applied.
Data type (optional): Any optional parameter can be used to specify the datatype of the aggregated column.

groupKind (optional): Specifies how to handle grouping. The options are:

GroupKind.Local
GroupKind.Global

comparer (optional): A function to compare keys for grouping. This is rarely used but can be helpful for custom grouping logic.

The GroupKind parameter specifies the kind of grouping. The GroupKind parameter supports the following two values: • GroupKind.Local A local group is formed from a consecutive sequence of rows from an input table with the same key value. • GroupKind.Global A global group is formed from all rows in an input table with the same key value.

In the Table.Group function GroupKind.Global is the default if it is not explicitly specified.

Example: Let’s group the rows in the table by DepartmentID, and create an aggregated column named "Total Score" which contains the sum of the “Total Science Marks of each Department” column for each group, and “Maximum Math Marks of each Department” column which returns the maximum “Math_Marks” for each group.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [DepartmentID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks = 65], 
      [DepartmentID = 1, Name = "Katrina", Science_Marks = 130, Math_Marks = 50], 
      [DepartmentID = 2, Name = "Alia", Science_Marks = 38, Math_Marks = 40], 
      [DepartmentID = 2, Name = "Vicky", Science_Marks = 48, Math_Marks = 90], 
      [DepartmentID = 1, Name = "Mohini", Science_Marks = 28, Math_Marks = 30], 
      [DepartmentID = 3, Name = "Meenakshi", Science_Marks = 29, Math_Marks = 25], 
      [DepartmentID = 3, Name = "Esha", Science_Marks = 100, Math_Marks = 50], 
      [DepartmentID = 2, Name = "Anjali", Science_Marks = 10, Math_Marks = 38]
    }
  ), 
  Return = Table.Group(
    MyTable, 
    "DepartmentID", 
    {
      {"Total Science Marks of each Department", each List.Sum([Science_Marks])}, 
      {"Maximum Math Marks of each Department", each List.Max([Math_Marks])}
    }
  )
in
  Return

The output of the above code is shown below:

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [DepartmentID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks = 65], 
      [DepartmentID = 1, Name = "Katrina", Science_Marks = 130, Math_Marks = 50], 
      [DepartmentID = 2, Name = "Alia", Science_Marks = 38, Math_Marks = 40], 
      [DepartmentID = 2, Name = "Vicky", Science_Marks = 48, Math_Marks = 90], 
      [DepartmentID = 1, Name = "Mohini", Science_Marks = 28, Math_Marks = 30], 
      [DepartmentID = 3, Name = "Meenakshi", Science_Marks = 29, Math_Marks = 25], 
      [DepartmentID = 3, Name = "Esha", Science_Marks = 100, Math_Marks = 50], 
      [DepartmentID = 2, Name = "Anjali", Science_Marks = 10, Math_Marks = 38]
    }
  ), 
  Return = Table.Group(
    MyTable, 
    "DepartmentID", 
    {
      {"Total Science Marks of each Department", each List.Sum([Science_Marks]), type number}, 
      {"Maximum Math Marks of each Department", each List.Max([Math_Marks]), type number}
    }
  )
in
  Return

The output of the above code is shown below:

Let’s use the GroupKind.Local parameter with the same code as above.

Example: Let’s group the rows in the table with group kind GroupKind.Local.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [DepartmentID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks = 65], 
      [DepartmentID = 1, Name = "Katrina", Science_Marks = 130, Math_Marks = 50], 
      [DepartmentID = 2, Name = "Alia", Science_Marks = 38, Math_Marks = 40], 
      [DepartmentID = 2, Name = "Vicky", Science_Marks = 48, Math_Marks = 90], 
      [DepartmentID = 1, Name = "Mohini", Science_Marks = 28, Math_Marks = 30], 
      [DepartmentID = 3, Name = "Meenakshi", Science_Marks = 29, Math_Marks = 25], 
      [DepartmentID = 3, Name = "Esha", Science_Marks = 100, Math_Marks = 50], 
      [DepartmentID = 2, Name = "Anjali", Science_Marks = 10, Math_Marks = 38]
    }
  ), 
  Return = Table.Group(
    MyTable, 
    "DepartmentID", 
    {
      {"Total Science Marks of each Department", each List.Sum([Science_Marks]), type number}, 
      {"Maximum Math Marks of each Department", each List.Max([Math_Marks]), type number}
    }, 
    GroupKind.Local
  )
in
  Return

The output of the above code is shown below:

Advanced use of each We can use each to perform more complex operations on each group.

Example: Custom Aggregation Calculate the difference between the maximum and minimum Science_Marks for each group:

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [DepartmentID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks = 65], 
      [DepartmentID = 1, Name = "Katrina", Science_Marks = 130, Math_Marks = 50], 
      [DepartmentID = 2, Name = "Alia", Science_Marks = 38, Math_Marks = 40], 
      [DepartmentID = 2, Name = "Vicky", Science_Marks = 48, Math_Marks = 90], 
      [DepartmentID = 1, Name = "Mohini", Science_Marks = 28, Math_Marks = 30], 
      [DepartmentID = 3, Name = "Meenakshi", Science_Marks = 29, Math_Marks = 25], 
      [DepartmentID = 3, Name = "Esha", Science_Marks = 100, Math_Marks = 50], 
      [DepartmentID = 2, Name = "Anjali", Science_Marks = 10, Math_Marks = 38]
    }
  ), 
  Return = Table.Group(
    MyTable, 
    "DepartmentID", 
    {
      {
        "Difference b/w Max and min Science Marks of each Department", 
        each List.Max([Science_Marks]) - List.Min([Science_Marks]), 
        type number
      }, 
      {"Row Count", each Table.RowCount(_)}
    }
  )
in
  Return

The output of the above code is shown below:

Previous Next