How to use Power Query Group By to summarize data

In this post, we look at the Power Query Group By transformation. Group By summarizes data by aggregating rows based on one or more columns.

In Excel, there are many ways to group and summarize data. We might use techniques such as:

Functions – Dynamic Array functions along with SUMIF(S), SUMPRODUCT, or AGGREGATE.
Pivot Tables
Subtotal feature (Data > Subtotal)

In Power Query, Group By performs a similar task, but the purpose is slightly different. In Excel, we generally summarize to display a result; however, in Power Query, we summarize to reduce data to the optimal granularity level.

The Power Query Group By transformation is simple yet powerful. So, let’s get started.

Table of Contents

Scenario
Two views of Power Query Group By

Basic Group By
Advanced Group By

Summarize Text
Conclusion

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0116 Power Query Group By.xlsx

Get access

Watch the video

Summarizing Data with Group By in Power Query

Watch the video on YouTube

Scenario

The example file contains just one Table. The Table includes sales data for January 2019 with Date, Customer, Product, Sold By, and Value columns.

Select a cell in the Table and click Data > From Table/Range from the Ribbon to load the data into Power Query.

The data loads into the Power Query editor.

The Group By feature is found in two places:

Home Ribbon: Home > Group By
Transform Ribbon: Transform > Group By

Choose either option to open the Group By dialog box.

Two views of Power Query Group By

The Group By dialog box comes in two forms:

Basic: allows a single column and a single aggregation to summarize by
Advanced: allows multiple aggregations based on multiple columns

The initial view depends on the number of columns selected within Power Query. If one column is selected within the preview window, the dialog defaults to the basic view; otherwise, the advanced view opens.

To switch between basic and advanced views, click the dialog box’s radio buttons.

We will look at both views in this section.

Basic Group By

To display the basic options, ensure the Basic button is selected.

In our example, let’s suggest we want to summarize our data to show the average product sale value. Select the following options: (these are shown in the screenshot below).

Column to group by: Product
New column name: Average Sale
Operation: Average
Column: Value

Click OK to close the dialog box and make the transformation.

The Preview Window looks like this:

The table shows the unique items from the Product column and the average sale value for each.

In the example, we used the average aggregation. The operation dropdown has the following options:

Sum
Average
Median
Min
Max
Count Rows
Count Distinct Rows
All Rows (which creates a table of each grouped item)

Advanced Group By

Click the Advanced button in the dialog box to show the advanced options.

Advanced allows us to summarize and calculate based on as many columns as we need.

For this example, let’s say we want the total and count of sales transactions by product and customer.

Set the first section to include the Product and Custom columns. Click the Add Grouping button to add columns, or the three dots ( … ) to remove columns.

In the new column name section, apply the following settings:

New column name: Total Sales
Operation: Sum
Column: Value

Click the add aggregation button, then set the new column as follows

New column name: Transactions
Operation: Count Rows
Column: [not required for Count Rows]

Click OK to perform the transformation.

The Preview Window now looks like this:

Data Summarized by Product and by Column

The table shows the unique items from the Product and Customer columns with the Sum and Count operators applied. While each column may contain duplicates, the combination of columns is unique.

Summarize Text

In the operations list in the dialog box, there are only numeric transformations (Sum, Average, Min, etc.). However, we can perform text aggregations too. We just need to make a few edits to the generated M code.

Let’s suggest we want a list of every person who sold to each customer. Apply the following parameters in the Group By dialog box:

When clicking OK, it generates an error in the Sales By column, as Sum cannot be performed on a text value.

However, in the M code, we can change this:

= Table.Group(#"Changed Type", {"Customer"}, {{"Sales By", each List.Sum([Sold By]), type nullable text}})

To this:

= Table.Group(#"Changed Type", {"Customer"}, {{"Sales By", each Text.Combine(List.Distinct([Sold By]),","), type nullable text}})

We now have a list of salespeople for each customer.

This proves that even though text operations are not listed in the user interface, we can still edit the M code to achieve this outcome.

Conclusion

Hopefully, you agree, the Power Query Group By transformation is a simple yet powerful feature.

We have seen it works with numbers and text (if we make some changes to the M code).

Power Query primarily prepares data for use in Excel or a Data Model. Therefore, we tend to use Group By to reduce the amount of information to the lowest level of granularity required, rather than to create reports.

For other examples of Group By, check out these posts:

For some advanced Group By techniques, check out this video: https://www.youtube.com/watch?v=jLpgt-wptH4

Read more posts in this series