Group by several keys and sort the output
Choose the grain of a summary table and present it in a deliberate order.
~8 min read · intermediate · Module 13: Group by and aggregation
By Tahir Nas · Examples run in our simulator · Updated
Before this: Group by with several aggregations
The key columns of a Group By decide the grain of the result: one row per distinct combination of their values. Group by Region and you get one row per region. Add Product and you get one row per region and product that actually occurs in the data.
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", "Product"}, {{"Total", each List.Sum([Amount]), type number}}),
Sorted = Table.Sort(Grouped, {{"Region", Order.Ascending}, {"Total", Order.Descending}})
in
SortedResult
| Region | Product | Total |
|---|---|---|
| North | Kite | 180 |
| North | Reel | 40 |
| South | Kite | 95 |
| South | Reel | 80 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Only combinations that exist
Group By never invents rows. If South had sold no reels, there would be no South/Reel row, not a row with 0. When a report needs every combination, build the full list of combinations separately (for example by merging a region list with a product list) and merge the totals onto it.
Accidental extra grain
Every key column splits the groups further. Grouping by Region, Rep and Product when the question was 'sales per region and product' gives North/Kite twice, once for Ana and once for Cleo. Choose the keys from the question, not from the columns that happen to be there:
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}
}),
TooFine = Table.Group(Sales, {"Region", "Rep", "Product"}, {{"Total", each List.Sum([Amount]), type number}})
in
Table.RowCount(TooFine)Result
5
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Sort for the reader
Sort the summary in the order people will read it: usually the outer key ascending and the measure descending within it, as in the first example. Because grouped output order isn't guaranteed, this sort step is part of the specification, not decoration.
Where to find this in Excel
- Excel for Windows
- Transform > Group By > Advanced > Add grouping
- What you see
- Each Add grouping row adds another key column to group on.
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
- Adding key columns that weren't part of the question and splitting groups.
- Expecting Group By to produce rows for combinations with no data.
- Leaving the summary unsorted and relying on whatever order appears.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Group By → Advanced lets you add several grouping columns.
- In Power BI, summarising in Power Query fixes the grain at refresh time; summaries that should respond to slicers belong in DAX measures instead.
- The simulator groups on exact values, with an optional comparer such as Comparer.OrdinalIgnoreCase as the fifth argument.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Group by and aggregation quiz