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.

Downloads

To work along with the examples below, download the sample files.  Click here, to subscribe and get access to the Downloads section.

Excel Downloads

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.

Conclusion

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.

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:

Leave a Reply

Your email address will not be published. Required fields are marked *