Using Group By in Power Query

Group By is an excellent feature in Power Query to group related rows by a column. This is useful when you have multiple entries for related rows and you want to combine them into one row based on a common attribute value. This example will illustrate this in detail

Consider a query with sales orders for employees across different states and dates.

Rows 2 and 3 has the sales orders for Bob in WA on 9/08/2016. There might be further such rows in the query where related rows might have multiple Orders. We want to combine the data such that there is only one row for each combination of Employee, State, and Date and an Orders column which sums up the orders for related rows.

Click on the Date column and go to Transform and select Group By to bring up the Group By window. Select the Advanced option at the top and fill out the sections like below and hit OK.



 

The Query now looks like this. Bob’s orders on 09/08/2016 in WA has now been summed to 62 in the Number of Orders columns and since there were two orders for Bob, the Count Orders column reflects that.



 
 

Check out our Power BI courses here.