Dynamic Group By Operations in Power Query M

The Table.Group function in Power Query summarizes columns and lets you perform calculations on your data. However, it often hardcodes both the column names and column types. If you make changes to your data earlier in the query, this can cause problems. This article will show you strategies to avoid these issues and use dynamic group by operations.

Table of contents

• Introduction
  • Overview of Table.Group
  • Issues with Hardcoded Grouping
• Strategy 1: Using Table.Combine
  • Removing Hardcoded Column Names and Data Types
  • Implementation of Table.Combine
• Strategy 2: Setting Data Types with Value.Type
  • Dynamically Retrieve Table Type
  • Handling Column Expansion Dynamically
• Download
• Conclusion

If you prefer video, here’s the video tutorial:

Introduction

We will work with the following table:

Overview of Table.Group

When you group your data in Power Query using the ‘All Rows’ operation, it summarizes your values and creates a new column containing a table object. This table object holds the grouped rows, which you can inspect further. For example, you can group your data by ‘Product’ and ‘Category’ and summarize the grouped rows using the following setup:

The output of the grouping operation is a summary of your data.

To introduce the issue, let’s look into the code generated by the Group By operation. Here’s the code that results from performing the grouping by ‘Product’ and ‘Category’:

Table.Group(
  Source, 
  {"Product", "Category"}, 
  {
    {
      "Details", 
      each _, 
      type nullable table [
        Date = nullable date, 
        LocationID = nullable text, 
        Product = nullable text, 
        Category = nullable text, 
        Quantity = nullable Int64.Type, 
        Amount = nullable Int64.Type
      ]
    }
  }
)

Notice the third argument in the Table.Group operation? This argument specifies the aggregations. An aggregation consists of a list with three elements:

• Column Name: This is the name of the column that the operation will create.
• Function for the aggregation: This specifies the calculation to perform on the summarized rows.
• Data type: This ascribes a data type to the newly created column.

A common operation is to add an index column to each of the created groups. To make this example more interesting, we will add an index column using the Table.AddIndexColumn function. Here’s how you can incorporate it into the aggregation function:

Table.Group(
  Source, 
  { "Product", "Category" }, 
  {
    {
      "Details", 
      each Table.AddIndexColumn( _, "Index", 1 ), 
      type nullable table [
        Date = nullable date, 
        LocationID = nullable text, 
        Product = nullable text, 
        Category = nullable text, 
        Quantity = nullable Int64.Type, 
        Amount = nullable Int64.Type
      ]
    }
  }
)

The used expression is valid and specifies a detailed table type.

Issues with Hardcoded Grouping

While the table type with hardcoded columns and data types for the Details aggregation is valid, it can lead to potential errors. Here’s why:

• New Columns: If in the future new columns are added before the Group By step, they won’t be included in the hard-coded table type definition. This means these columns won’t be available in the expand table operation. And when a column name isn’t specified in the table type, the expand table operation doesn’t suggest it, as shown below. You could then manually add the new column to the expand table operation, but it wouldn’t have a data type assigned.

• Type Changes: Another issue arises when the type of a column is changed earlier in your query before the Group By step. This change isn’t automatically reflected in the table type definition. For example, if you change the LocationID from type text to type number earlier in your query, this change won’t be shown in the steps following the Group By operation. And that’s not the only issue. You may even run into an error when your column has a data type mismatch and you load your data into Power BI.

To avoid these issues, it’s important to rework the logic to make it dynamic. There are two main strategies:

1. Using Table.Combine
2. Setting Data Types with Value.Type

Let’s see how both of these work.

Strategy 1: Using Table.Combine

The first strategy is useful for when all the columns you need are included in the table object we stored in the ‘Details’ column. Instead of hardcoding all the column names and data types, we can make use of the Table.Combine function. So how does tha twork?

Removing Hardcoded Column Names and Data Types

First, we remove the complex table type definition and only specify type table. Here’s what that looks like:

Table.Group(
  Source, 
  { "Product", "Category" }, 
  { { "Details", each Table.AddIndexColumn( _, "Index", 1 ), type table } }
)

This makes our code cleaner and shorter. The outcome of this operation is a table with a ‘Details’ column that contains the summarized table objects.

Implementation of Table.Combine

To expand the table, we will avoid using the default expand column operation (which uses Table.ExpandTableColumn) provided by the user interface. Instead, we’ll use the Table.Combine function on each of the table objects in the column. This requires referencing both the previous step name (e.g., #"Grouped Rows") and the column name. Here’s how you can do it:

Table.Combine( #"Grouped rows"[Details] )

This operation not only expands the rows in the table object but also maintains the original data types.

The advantage of this approach is that whenever new columns are created at an earlier stage in your query, the Group By operation will automatically pick them up and expand them without requiring any changes to your code.

Strategy 2: Setting Data Types with Value.Type

In the next scenario, we slightly modify our setup to include an additional aggregation that counts the number of rows in each group. We achieve this using the following Group By operation:

In this setup, the Count Rows operation creates a column with the number of rows that are grouped. The output of this operation looks like this:

To illustrate this example, we should include multiple aggregations in the grouping operation. Besides counting the rows, let’s add an index column for each of the grouped table objects.

Table.Group(
  Source, 
  { "Product", "Category" }, 
  {
    { "Count", each Table.RowCount(_), Int64.Type }, 
    { "Details", 
      each Table.AddIndexColumn( _, "Index", 1 ), 
      type nullable table [
        Date = nullable date, 
        LocationID = nullable text, 
        Product = nullable text, 
        Category = nullable text, 
        Quantity = nullable Int64.Type, 
        Amount = nullable Int64.Type
      ]
    }
  }
)

Unfortunately, the Table.Combine method only retrieves the columns values specified in the Details aggregation (rows 6 – 16). That means all other columns are discarded, including the Count aggregation. Therefore that method is not an option. For the second method we will therefore look at how we can instead define a table type dynamically.

Dynamically Retrieve Table Type

Let’s first have a look at how we can retrieve the data type of a value. To do that we can make use of the Value.Type function. When applied to a table, this function returns a table type that includes the data type definitions of each column. Here’s how to extract the table type from the source table:

/* Output: 
type nullable table [
        Date = nullable date, 
        LocationID = nullable text, 
        Product = nullable text, 
        Category = nullable text, 
        Quantity = nullable Int64.Type, 
        Amount = nullable Int64.Type
      ] */

Value.Type( Source )

The output of this operation is a table type containing the data type definitions of each column within the Source table. Unfortunately, the Source table does not contain all the relevant columns. As you may remember, we also included an index column during the Group By operation. To include this, instead of referencing the Source table by itself, we could extract the table type from a table that includes the index column:

Value.Type( Table.AddIndexColumn( Source, "Index", 1 ) )

This output is a table type that contains all the necessary columns. We can improve this further, and not experience a performance penalty, by adding this information to an empty table.

Value.Type( Table.AddIndexColumn( Table.FirstN( Source, 0 ), "Index", 1 ) )

We can then incorporate this logic into our Group By expression as shown below:

Table.Group(
  Source, 
  { "Product", "Category" }, 
  {
    { "Count", each Table.RowCount(_), Int64.Type },
    {
      "Details", 
      each Table.AddIndexColumn( _, "Index", 1 ),
      Value.Type( Table.AddIndexColumn( Table.FirstN( Source, 0 ), "Index", 1 ) )
    }
  }
)

By removing hardcoded columns within the table type, our code becomes more concise and dynamic. This approach makes sure that any changes in the table structure are automatically handled.

Handling Column Expansion Dynamically

To extract columns while keeping data types intact, we must address the issue of hardcoding the column values in the expand operation. The code generated for expanding all columns in our example looks like this:

Table.ExpandTableColumn(
  #"Grouped rows", 
  "Details", 
  { "Date", "LocationID", "Product", "Category", "Quantity", "Amount", "Index" }, 
  { "Date", "LocationID", "Product.1", "Category.1", "Quantity", "Amount", "Index" }
)

To make the expand table column operation dynamic, we need to perform a few steps.

1. Remove the Fourth Argument: The fourth argument of Table.ExpandTableColumn is optional and is used to rename the expanded columns. If we want to keep our column names the same, we can remove this argument.
2. Dynamically Retrieve Columns: Instead of hardcoding the columns to expand, we should dynamically retrieve them from the table. Power Query does not allow duplicate column names, so any columns already present in the original table, such as ‘Product’ and ‘Category’, should not be part of the expand columns operation.

To achieve this, we keep the first two arguments the same and dynamically compute the columns to expand for the third parameter. Here’s how to do it:

Table.ExpandTableColumn(
  #"Grouped rows", 
  "Details", 
  let
    // Retrieves all columns available in the grouped tables
  AllColumns  = Table.ColumnNames( #"Grouped Rows"[Details]{0} ),
    /* Retrieves the columns from the Group By table
       This includes columns "Product", "Category", "Count" and "Details" */
  ColumnsToRemove  =  Table.ColumnNames( #"Grouped Rows" ),
    // Specifies the columns to expand from the table object
  ColumnsToExpand  = List.RemoveItems( AllColumns, ColumnsToRemove )
 in
    ColumnsToExpand  
)

This setup retrieves the column names from both the #”Grouped Rows” step and the grouped table objects. It then generates a list of column names that exist in the table object but do not yet exist in the grouped query by using the List.RemoveItems function. Finally, the ColumnsToExpand variable, which stores this list, is used to dynamically expand the table.

Download

To play with this code yourself, you can download the file below:

Conclusion

The user interface of Power Query is convenient and easy to use. However, it often generates code that hardcodes operations, which can cause problems when your data changes. For example, if new columns are added or data types are changed earlier in your query, the hardcoded operations might not handle these changes well. This can lead to errors or require manual updates to your code.

With a bit of custom M code, you can make your Power Query solutions more adaptable. Strategies like using Table.Combine and setting data types dynamically with Value.Type help prevent these issues. Instead of tedious manual updates, your queries will retrieve the relevant columns and data types themself.