Power QueryExercises

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
    Sorted

Result

RegionLinesTotalAverageReps
North322073.333333333333332
South217587.52

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

FunctionResult
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.
Functions in this lesson:Table.GroupList.SumList.AverageList.DistinctTable.RowCount

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.