Group by with several aggregations
Build summary tables with the right aggregation for each question.
~10 min read · intermediate · Module 13: Group by and aggregation
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Sort rows on more than one column
Group By collapses rows that share key values into one row per group, and calculates aggregations over each group's rows. One step can hold several aggregations.
let
Sales = #table({"Region", "Rep", "Product", "Amount"}, {
{"North", "Ana", "Kite", 120},
{"South", "Ben", "Reel", 80},
{"North", "Cleo", "Kite", 60},
{"North", "Ana", "Reel", 40},
{"South", "Dev", "Kite", 95}
}),
Grouped = Table.Group(Sales, {"Region"}, {
{"Lines", each Table.RowCount(_), Int64.Type},
{"Total", each List.Sum([Amount]), type number},
{"Average", each List.Average([Amount]), type number},
{"Reps", each List.Count(List.Distinct([Rep])), Int64.Type}
}),
Sorted = Table.Sort(Grouped, {{"Region", Order.Ascending}})
in
SortedResult
| Region | Lines | Total | Average | Reps |
|---|---|---|---|---|
| North | 3 | 220 | 73.33333333333333 | 2 |
| South | 2 | 175 | 87.5 | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Inside each aggregation, _ is the group's table and [Amount] is that group's Amount column as a list. That's why list functions do the work.
Rows versus values
- Count Rows (
Table.RowCount(_)) counts rows, including ones with null in some column. - Distinct values:
List.Count(List.Distinct([Rep])). North has three lines but only two reps, because Ana appears twice. - Count Distinct Rows in the editor counts distinct entire rows of the group, which is rarely what 'how many reps' means.
Sort the result
Group By doesn't promise any particular output order. If the report expects regions alphabetically, add a sort step, as above. Graded exercises on this site accept any order unless the task asks for one.
nulls in aggregations
#table({"Function", "Result"}, {
{"List.Sum({4, null, 6})", List.Sum({4, null, 6})},
{"List.Average({4, null, 6})", List.Average({4, null, 6})},
{"List.Sum({null, null})", List.Sum({null, null})},
{"List.Count({4, null, 6})", List.Count({4, null, 6})},
{"List.NonNullCount({4, null, 6})", List.NonNullCount({4, null, 6})}
})Result
| Function | Result |
|---|---|
| List.Sum({4, null, 6}) | 10 |
| List.Average({4, null, 6}) | 5 |
| List.Sum({null, null}) | null |
| List.Count({4, null, 6}) | 3 |
| List.NonNullCount({4, null, 6}) | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Give every aggregation a type, as in the example. Without one, the new column is type any, which loads as text in Power BI and can't be summed in a visual.
Where to find this in Excel
- Excel for Windows
- Transform > Group By (also on the Home tab)
- What you see
- Advanced lets you group on several columns and add several new columns, each with a name, an operation and a source column.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Grouping or summarizing rows (Microsoft Learn).
Common mistakes
- Using Count Distinct Rows when the question is how many distinct values one column has.
- Expecting grouped output to come out sorted.
- Averaging a column where null should have counted as 0 (or the reverse) without deciding which is right.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Home → Group By, with Basic (one key, one aggregation) and Advanced (several keys and aggregations) modes.
- The dialog's operations are Sum, Average, Median, Min, Max, Count Rows, Count Distinct Rows and All Rows; anything else is a small edit in the formula bar.
- The simulator returns groups in first-seen order (KD02); native hosts don't guarantee an order, so sort if it matters.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Summarise orders by region (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- intermediate~8 minPro
Summarise orders by region (kite shop)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Summarise orders by region (bakery)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Summarise orders by region (bike rental)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Count distinct customers per product (kite shop)
For each product, count how many different customers ordered it.
- intermediate~8 minPro
Count distinct customers per product (bakery)
For each product, count how many different customers ordered it.
- intermediate~8 minPro
Count distinct customers per product (bike rental)
For each product, count how many different customers ordered it.
- advanced~10 minPro
Show each region's share of total revenue (kite shop)
Total revenue per region, then each region's percentage of the grand total.
- advanced~10 minPro
Show each region's share of total revenue (bakery)
Total revenue per region, then each region's percentage of the grand total.
- advanced~10 minPro
Show each region's share of total revenue (bike rental)
Total revenue per region, then each region's percentage of the grand total.
- intermediate~8 minPro
Find each region's first and last order date (kite shop)
Group by Region with the earliest and latest OrderDate as real dates.
- intermediate~8 minPro
Find each region's first and last order date (bakery)
Group by Region with the earliest and latest OrderDate as real dates.
- intermediate~8 minPro
Find each region's first and last order date (bike rental)
Group by Region with the earliest and latest OrderDate as real dates.
- beginner~6 minPro
Summarise orders by region and product (kite shop)
Group by both Region and Product with the number of orders and the total quantity.
- beginner~6 minPro
Summarise orders by region and product (bakery)
Group by both Region and Product with the number of orders and the total quantity.
- beginner~6 minPro
Summarise orders by region and product (bike rental)
Group by both Region and Product with the number of orders and the total quantity.
- advanced~12 minPro
Find each region's best-selling product by revenue (kite shop)
Total revenue per region and product, then keep the top product of each region.
- advanced~12 minPro
Find each region's best-selling product by revenue (bakery)
Total revenue per region and product, then keep the top product of each region.
- advanced~12 minPro
Find each region's best-selling product by revenue (bike rental)
Total revenue per region and product, then keep the top product of each region.
- intermediate~8 minPro
Average quantity per region when some quantities are missing (kite shop)
Some orders have no quantity yet. Report orders, how many have a quantity, and the average of the known quantities.
- intermediate~8 minPro
Average quantity per region when some quantities are missing (bakery)
Some orders have no quantity yet. Report orders, how many have a quantity, and the average of the known quantities.
- intermediate~8 minPro
Average quantity per region when some quantities are missing (bike rental)
Some orders have no quantity yet. Report orders, how many have a quantity, and the average of the known quantities.
- intermediate~8 minPro
List each customer's products in one cell (kite shop)
One row per customer with their distinct products, sorted alphabetically and separated by commas.
- intermediate~8 minPro
List each customer's products in one cell (bakery)
One row per customer with their distinct products, sorted alphabetically and separated by commas.
- intermediate~8 minPro
List each customer's products in one cell (bike rental)
One row per customer with their distinct products, sorted alphabetically and separated by commas.
- beginner~6 minPro
Keep only customers with three or more orders (kite shop)
Count orders per customer and keep the customers who ordered at least three times, with their total quantity.
- beginner~6 minPro
Keep only customers with three or more orders (bakery)
Count orders per customer and keep the customers who ordered at least three times, with their total quantity.
- beginner~6 minPro
Keep only customers with three or more orders (bike rental)
Count orders per customer and keep the customers who ordered at least three times, with their total quantity.
- advanced~12 minPro
Interview task: second-highest salary per department (logistics)
For each department, return the second-highest distinct salary. Departments with only one distinct salary are left out.
- advanced~12 minPro
Interview task: second-highest salary per department (software)
For each department, return the second-highest distinct salary. Departments with only one distinct salary are left out.
- advanced~12 minPro
Interview task: second-highest salary per department (hospital)
For each department, return the second-highest distinct salary. Departments with only one distinct salary are left out.