Grouping or Summarizing Rows in Power Query

Updated on
3
Power Query
Photo of author
Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

How to Summarize Your Data Using Power Query - Part 1

Grouping rows in Power Query can help show your data in a summarized way. You group rows by one or more columns and perform aggregations.

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.

You can find the video tutorial on the beginning of this article. If you would rather read the tutorial or need more instructions, then continue reading.

Importing a Table into Power Query

Group data in Power Query

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
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.

Grouping or Summarizing 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.

Use group By in power query

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.

Specify 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.

Close and load your data

To move this data to the worksheet you:

  • click on Close & Load -> press Close & Load To…
Import-Data as a table to worksheet

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
End result Summarized Table

The resulting table is now imported onto the Excel worksheet.

Conclusion

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.

You can for example use Fuzzy Grouping for grouping items that are similar. And the next post shows 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!

Recommended Reading…

Power Query Foundations

Beginners Guide to Power Query

List Functions (200+ Examples)

Text Functions (150+ Examples)

Creating Tables (40+ Examples)

If Statements in Power Query

Generate the Ultimate Date Table

Advanced Topics

Master List.Generate with Easy Examples

Deep Dive into List.Accumulate

Create Calendar with a Dynamic Language

Replacing Values (Beyond the User Interface)

Share this post:

Import Multiple Files from a Folder with Power Query

How to Group By Maximum Value using Table.Max – Power Query

Latest from my blog

Power Query

Understanding Semi Joins in Power Query M

Power Query

Creating a 445 Calendar (incl 454, 544) in Power Query M

Power Query

Using Variables in Power Query M

Power Query

Convert Unix Timestamp to Datetime in Power Query M

Power Query

Extracting Year, Month, or Day from a Date in Power Query

Power Query

Dynamic Group By Operations in Power Query M

3

Comments

Leave a Reply

  1. Any advice to summarize several columns in bulk?, Iet’s say i have a table with multiple columns ( 10 or more) and i need to get the total sum of each one, with the User Interface i neet to do it one by one. Thanks

    Reply

  2. Thanks Rick,

    Something you shared around grouping of data and adding an index lead to me attempting something in one of my Power BI files.

    I have a created groups, added an index and it all seems to be working, but I have a problem. Some of the records for each grouping fall on the same date, which throws the data into the wrong pattern. So I thought, no problem I will sort the table by date and then by another column to get what I need (multiple sort). But the problem is that when I group the data, the sorting disappears…? And when I tried to add ‘table.buffer()’ in a prior step, it completely messes with the result.

    Can you advise, how do I sort the columns in a grouping prior to adding an index?

    Reply

    • Absolutely.

      Imagine you’re grouping your data by Color. The Table.Group function could be something like:

      Table.Group(
Source,
{"Color"},
{ {
 "Allrows",
 each _,
 type table [Color= nullable text, Date= nullable text]
} }
)

      You can also include the sorting and index argument within this:

      Table.Group(
Source,
{"Color"},
{ {
 "Allrows",
 each Table.AddIndexColumn(Table.Sort(_, {"Date", Order.Ascending}), "Index", 0),
 type table [Color= nullable text, Date = nullable text]
} }
)

      However, in my experience the Table.Buffer function works fine too. If you first sort your data, then Buffer the result. You can Group the data and my tests so far have shown to give a good result. Some more info on Table.Buffer and its effects you can find here: https://gorilla.bi/power-query/removing-duplicates/

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About BI Gorilla

BI Gorilla is a blog about DAX, Power Query and Power BI. It was founded in 2018 by Rick de Groot with the goal to provide easy to understand resources to help you advance.

Latest Posts

M Language Function Reference

You can now find everything you need to know on the Power Query M language (syntax, examples, articles etc.) at https://powerquery.how/.

Newsletter

Free your mind, automate your data cleaning. Sign up

©️ 2024 BI Gorilla. Powered by Rocket.net, FlyingPress • Built on theme GeneratePress

Blog Contact Privacy Policy