Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to use Power Query Group By to summarize data

Power Query Group By

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.

Download the example file

I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be helpful for future reference.

Download Icon

Download the file: Power Query Group By.xlsx

Watch the video

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.

Example Data for Group By

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

Data from Table or Range

The data loads into the Power Query editor.

The Group By feature is found in two places:

  • Home Ribbon: Home > Group By
    Home Group By
  • Transform Ribbon: Transform > Group By
    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
Power Query Group By Dialog Box

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

The Preview Window looks like this:

Data Summarized by Product

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]
Advanced Group By

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:

Group By with Text

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.

Group By after text transformation

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

  1. Introduction to Power Query
  2. Get data into Power Query – 5 common data sources
  3. DataRefresh Power Query in Excel: 4 ways  &  advanced options
  4. Use the Power Query editor to update queries
  5. Get to know Power Query Close & Load options
  6. Power Query Parameters: 3 methods
  7. Common Power Query transformations (50+ powerful transformations explained)
  8. Power Query Append: Quickly combine many queries into 1
  9. Get data from folder in Power Query: combine files quickly
  10. List files in a folder & subfolders with Power Query
  11. How to get data from the Current Workbook with Power Query
  12. How to unpivot in Excel using Power Query (3 ways)
  13. Power Query: Lookup value in another table with merge
  14. How to change source data location in Power Query (7 ways)
  15. Power Query formulas (how to use them and pitfalls to avoid)
  16. Power Query If statement: nested ifs & multiple conditions
  17. How to use Power Query Group By to summarize data
  18. How to use Power Query Custom Functions
  19. Power Query – Common Errors & How to Fix Them
  20. Power Query – Tips and Tricks

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published.