Power QueryExercises

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
    Expanded

Result

RegionRepAmountRank
NorthAna1201
NorthCleo602
NorthAna403
SouthDev951
SouthBen802

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
    Expanded

Result

RegionRepProductAmountShare
NorthAnaKite1200.5454545454545454
NorthCleoKite600.2727272727272727
NorthAnaReel400.18181818181818182
SouthBenReel800.45714285714285713
SouthDevKite950.5428571428571428

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Writing the group function as (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 each for 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.