Group By feature in Power Query help us to group or summarize data from different Rows on the basis of a condition or conditions.
Following is a sample Sales data from which I want to create a summary report. The data contains the sales amount for 3 products Snickers, Bounty and Galaxy for 4 different regions North, South, East and West.
![](https://xlncad.com/wp-content/uploads/2020/05/1_Data-3.png)
And following is the format of the Summary report which I want. i.e. the Total Sales amount for each product.
![](https://xlncad.com/wp-content/uploads/2020/05/2_Summary-1.png)
Let’s see how to create a summary from the above data using Power Query
Select a cell in sales data > In the Data tab > click on From Table/Range > Click OK in the Create Table Dialog
![](https://xlncad.com/wp-content/uploads/2020/05/3_Create-Table.png)
Data is loaded into the Power Query Editor.
![](https://xlncad.com/wp-content/uploads/2020/05/4_Data-loaded-into-PQE-1.png)
To group data on the basis of Products, Click on Group By in the Home tab
![](https://xlncad.com/wp-content/uploads/2020/05/5_Group-By.png)
In the Group By dialog, we have to define 4 things
- Column containing items to group – Product is the column containing items to group
- New column name [optional] – Specify the name of the new column
- Operation – Here, I want the sum of amounts for each product, so I have selected Sum as operation
- Column containing values – The column called Amount contains the values to sum
![](https://xlncad.com/wp-content/uploads/2020/05/6_Define-Group-1.png)
When I click OK in the Group By dialog, after defining the parameters, I will have a table like the following.
The column called Total Sales contains the total sales amount for each product.
![](https://xlncad.com/wp-content/uploads/2020/05/7_Summary.png)
To load this data into the worksheet, click on Close & Load in the Home tab > Close & Load to…
![](https://xlncad.com/wp-content/uploads/2020/05/8_Close-Load.png)
In the Import Data dialog, Select Existing worksheet > Select the cell where you want to place the data (here I have selected the cell F2) and click OK
![](https://xlncad.com/wp-content/uploads/2020/05/9_Import-Data-1.png)
and here, we have the summary of Total sales for each Product.
![](https://xlncad.com/wp-content/uploads/2020/05/10_Summary-loaded.png)
In the same manner we can group the above sales data for Region and create the summary for Region-wise sales.
![](https://xlncad.com/wp-content/uploads/2020/05/11_Summary-loaded-2.png)
Whenever you add something to the source table…
go to the Data tab in the Excel ribbon and click on the button called Refresh All to update the output tables
![](https://xlncad.com/wp-content/uploads/2020/05/12_Add-Data-and-Refresh.gif)