Power QueryExercises

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
    Sorted

Result

RegionProductTotal
NorthKite180
NorthReel40
SouthKite95
SouthReel80

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.

Five rows instead of four: the extra Rep key split North/Kite into two groups.

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