How to Group or Summarize Data – Power Query #2

Page Image - How to Group or Summarize Data

Many times, Excel users need to show their data in a summarized way. Go-to tools for many are PivotTables or formulas as SUMIFS, SUMPRODUCT, AGGREGATE and the like. Yet with Power Query, Excel users now have another tool to summarize their data.

Video Tutorial

If you would rather read the tutorial or need more instructions, then continue reading.

Importing a Table into Power Query

Power Query - Group By - 1 - Data Table

Above table contains the sales from April 2018. I’m interested in the amount sold per Product. Let’s get started. First of all:

  • select a cell within the table
Power Query - Group By - 2 - Import Table to Power Query
  • Next, go to the tab Data -> click From Table/Range

These steps import the table into Power Query so you can work with it.

Group By / Summarize data

The effect of grouping data is that it shows data on a higher (aggregated) level. For our data, I am interested in the sales per Product. Currently my data contains multiple rows within a single product group. Grouping By Product summarizes these rows into a single grouped row per product.

Power Query - Group By - 3 - Group By

First of all, to achieve this grouping:

  • go to the tab Home -> Group by

In this screen you can select the variable(s) to group your data by. You select Advanced only when you want to group by multiple variables or when you want to perform an operation on more than one variable. In this case you can leave the selection at Basic.

Power Query - Group By - 4 - Group By Options

Next, you can choose several options on how to output your data. For this exercise:

  • Group your data by ‘ProductDescription’
  • Name a column ‘Sales’, and SUM up the column ‘Amount’
  • Press “Ok”

 The below table is the result of these steps. What’s left is all unique values from the column ProductDescription and a column with the underlying sales values.

To move this data to the worksheet you:

  • click on Close & Load -> press Close & Load To…
Power Query - Group By - 6 - Import-Data

After, you get a menu to specify how you would like to import the data. In this case:

  • Select Table -> click on the Existing Worksheet -> select cell F5
  • Click OK
Power Query - Group By - 7 - Summarized-Table

The resulting table is now imported onto the Excel worksheet.

This post showed a very basic Group By operation in Power Query. If you are only interested in the sales per Product you are now done. Yet much more complex options are available for grouping your data.

The next post post continues on how to group your data and get the top seller in Power Query. Please leave a comment below with questions or suggestions. See you next time!

Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills. Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

3 Responses

Leave a Reply

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

Post comment