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

Power Query – Grouping and Summarizing Data

Power Query - Grouping and Summarizing

Looking back at this Power Query series, I can see that I have omitted to post about one of the more common and more powerful features; which is Group By.  We have used it a few times already, but in this post, I wanted to give it a more detailed look.

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

  • Functions – SUMIF(S), SUMPRODUCT, or the new Dynamic Array functions
  • Pivot Tables
  • Subtotal feature (Data -> Subtotal)

Now there is a new method… the Group By feature in Power Query.  Let’s learn how to use it.

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 useful for future reference.

Download Icon
Download the file: Power Query – Example Files

To keep things easier, we will use the same file as the previous part.

The examples in this post use the file called Example 15 – If Function.xlsx

The example

The example file contains just one Table.  Select a cell in the Table and click Data -> From Table/Range to load the data into Power Query.

Data - From Table or Range

Once in the Power Query Editor, the Group By feature is the first icon on the Transform tab: Transform -> Group By.  The icon appears elsewhere, in the user interface, but this is the easiest to find.

Transform Group By

Group By comes in basic and advanced forms, though the advanced options are pretty easy by themselves.

Basic Group By

The basic form provides a single column option and a single operation. To display the basic options, ensure the Basic button is selected (as shown in the screenshot below).

Group By - Basic

If working along with the example, select the following options (these are also shown in the screenshot above).

  • Column to use: Product
  • New column name: Average Sale
  • Operation: Average
  • Column: Value

This operation calculates the average value of each unique item in the Product column.  Click OK.

The Preview Window will look like this:

Group By - Basic Preview Window

There are lots of operators for us to use:

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

Advanced Group By

The advanced options give us the ability to use as many columns as we want. Click the Advanced button to display the options.

For this example, let’s say we wanted the total and count of sales transactions by Product and Customer.

Set the first box equal to Product.  Click the Add Grouping button, and set the second box equal to Customer.

Group By - Advanced column selection

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]

Group By - Advanced Operations

Click OK.

The Preview Window now looks like this:

This shows the data aggregated by the combined Product and Customer columns.  While each column may contain repeats, each pair is unique.


Hopefully you can agree that Group By is a simple, yet powerful feature.

In my mind, Power Query is about preparing data for use in Excel or a Data Model.  Therefore Group By is a conceptually tricky feature.  It’s excellent for creating summarized reports, but we don’t necessarily use Power Query to create reports in that way.  Therefore, I tend to use Group By to reduce down the amount of information down to the lowest level of granularity required, rather than for creating reports.

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

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet 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:

2 thoughts on “Power Query – Grouping and Summarizing Data

  1. David L Wise says:

    This is very helpful, and I’ve figured all this out myself just by playing in excel’s power query. What I haven’t figured out is how to include sub-totals and grand totals. I see where you say power query is best used to “prepare data for a report” rather than to create the report. So I’m assuming after your post here, you’d close and load to a new tab, then some how perform operations on that tab to add the sub and grand totals. Do you have a post that explains that?

    • Excel Off The Grid says:

      Hi David – It really depends on your scenario. Having got the data into a suitable format, the options for presenting the data are:
      1) Pivot Table / Pivot Chart
      2) Power Pivot
      3) CUBE Functions
      4) Load to Worksheet and use formulas to extract relevant information
      5) Create another query which references the source, and create the calculations.
      6) Power BI
      7) Other software which connects to Excel workbooks

      I don’t have any specific posts which go from Power Query to reporting. Each option is a whole area of focus in itself.

Leave a Reply

Your email address will not be published.