Generally, grouping means forming several things into a cluster or being brought together, the same way in power query we have a functionality called GroupBy which allows you to group by aggregation like sum, count, min, max, etc.
At the end of this article, there is an interesting tip which will be useful to you. Let’s begin
Let’s say you have a list of sales data with product name, region, and sale value, and from this you want to know what is a top-selling product, In Excel we use to take unique values of product and perform SUMIFS function to get the result which takes time, but in power query using GroupBy functionality you can complete in few clicks select GroupBy then group data by product and add(SUM) column sales value you will get the answer, the same way if you want to know the region-wise top-selling product you can group data by product, region and add(SUM) column sales value.
Where to find GroupBy button:
Group by button can be found in three places:
- On the home tab >> Transform group >> GroupBy.
- Go to transform tab >> GroupBy.
- Go to the column you want to group then right click using mouse/touchpad select group by.
List of Operations:
- Sum – Displays the total of the grouped rows.
- Average – Displays the average value of the grouped rows.
- Median – Displays the Median value i.e mid value of the grouped rows.
- Min – Displays the least value from the grouped rows.
- Max – Displays the highest value from the grouped rows.
- Count Rows – Displays the number of rows in a grouping.
- Count Distinct Rows – Displays the distinct number of rows in a grouping.
- All Rows – Creates a table for each grouping.
- Note: Not only these operations you can also perform many PowerQueryMFunctions after creating a table i.e. All Rows
Example for basic GroupBy
Here is a simple example to know how to use group by function. The following Table contains BSE data of 2 years which contains the Date, Name of the company, industry belonging to, Quantity, state, and classification.
From the above data let us find the total quantity sold by each industry, Before that let us see how to enter query editor(Power Query).
1. Convert the data to the table using CTRL+T or go to the Insert tab and select the table.
2. Go to Data tab and in Get & Transform Data group select from Table/Range.
3. Now you will be inside the query editor, before starting lets have a look at the basic GroupBy Dialogue box.
Step-1: Select the column you want to group then right-click on it and select GroupBy. Here we need to group Industry Classification so select right-click and select GroupBy.
Step-2: A GroupBy dialogue box will be displayed under that select Basic as we want to group only one column and then select the column you want to group. Here it is Industry Classification.
Step-3: Under New Column Name enter the column name you want to add.
Step-4: Under Operation select the type of operation you want to perform, here we are selecting Sum to get the total quantity.
Step-5: Under Column select the column for which you want to apply specified operation. and click on ok
Step-6: After Clicking OK you can see the result i.e total quantity sold by each industry.
Example for Advance GroupBy
From the same example let us bring industry-wise and month wise total quantity. before starting let us see the dialogue box of advance Group By Option.
Step-1: A GroupBy dialogue box will be displayed, here we are going to group more than one column so select the advanced option.
Step-2: Next select the first column you want to group in this case it is Industry Classification
Step-3: As we are going to add another column for grouping click on add grouping and select the column in this case it is Date.
Step-4: Under New Column Name enter the column name you want to add.
Step-5: Under Operation select the type of operation you want to perform, here we are selecting Sum to get the total quantity.
Step-6: Under Column select the column for which you want to apply specified operation. and click on ok
Step-7: After Clicking OK you can see the result i.e total quantity sold by each industry.
#Tip: If you look at the above picture grouped date is row-wise which is not good to present as a report, in the power query, there is a special functionality i.e. PIVOT COLUMN which turns it column-wise. Follow the steps to get it.
Step-1: Select the column you want to apply pivot column in this case it is Date.
Step-2: Go to Transform Tab and in any column group select pivot column.
Step-3: A Pivot Column dialogue box will be displayed under the values column select the column you want to pivot in this case it is the date.
Step-4: Click on advanced to enable more options.
Step-5: Under Aggregate value function we dont want to aggregate anything so select dont aggregate and click on ok.
Step-6: After Clicking ok you will get a proper report as shown in the below picture.