All Rows grouping and per-group calculations
Run a calculation inside each group and bring the detail rows back.
~11 min read · advanced · Module 13: Group by and aggregation
By Tahir Nas · Examples run in our simulator · Updated
Before this: Group by with several aggregations, Add index columns
Ordinary aggregations collapse a group to one value. All Rows keeps the group's rows as a nested table. You can then transform each nested table on its own (sort it, number it, compare each row with the group's total) and expand the results back into ordinary rows.
Rank within each group
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"}, {{"Rows", each Table.AddIndexColumn(Table.Sort(_, {{"Amount", Order.Descending}}), "Rank", 1, 1, Int64.Type), type table}}),
Expanded = Table.ExpandTableColumn(Grouped, "Rows", {"Rep", "Amount", "Rank"})
in
ExpandedResult
| Region | Rep | Amount | Rank |
|---|---|---|---|
| North | Ana | 120 | 1 |
| North | Cleo | 60 | 2 |
| North | Ana | 40 | 3 |
| South | Dev | 95 | 1 |
| South | Ben | 80 | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The index restarts in each group because it's added to each nested table separately. Adding it to the whole table would number all rows 1 to 5.
Share of the group's total
Inside the group, _ is the group's table, so its total is one List.Sum away. Name it in a small let so the inner each can use it:
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"}, {{"Rows", (g) =>
let
RegionTotal = List.Sum(g[Amount])
in
Table.AddColumn(g, "Share", each [Amount] / RegionTotal, type number), type table}}),
Expanded = Table.ExpandTableColumn(Grouped, "Rows", {"Rep", "Product", "Amount", "Share"})
in
ExpandedResult
| Region | Rep | Product | Amount | Share |
|---|---|---|---|---|
| North | Ana | Kite | 120 | 0.5454545454545454 |
| North | Cleo | Kite | 60 | 0.2727272727272727 |
| North | Ana | Reel | 40 | 0.18181818181818182 |
| South | Ben | Reel | 80 | 0.45714285714285713 |
| South | Dev | Kite | 95 | 0.5428571428571428 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
(g) => … instead of each gives the group a name, which avoids confusion with the inner each.When to use All Rows
- Ranking or numbering within groups.
- Comparing each row with a group-level figure (share, difference from average).
- Choosing a row per group by a rule (see which duplicate to keep).
- Keeping detail for a drill-down while also adding group totals.
Where to find this in Excel
- Excel for Windows
- Transform > Group By, with the All Rows operation
- What you see
- Each group's rows land in a nested [Table] value that you can preview and then work on per group.
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 an index to the whole table and calling it a rank within groups.
- Using
eachfor both the group function and the inner row function and losing track of which_is which. - Expanding the group key column again and getting duplicate columns.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Group By → operation All Rows creates the nested tables; the per-group transformation is a formula-bar edit or a custom column on the grouped table.
- All Rows keeps every detail row in memory per group; on large tables, filter and remove columns first.
- The simulator supports functions written as
(g) => …in Table.Group aggregations, as shown.
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: Rank each employee's claims by amount (consultancy)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.