Power QueryExercises

Group By recipes: count if, concatenate, first and last, top value

Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

The Group By dialog offers sum, average, min, max, count and all rows. The formula bar offers everything else. In each aggregation, _ is the group's table and [Column] is that column as a list.

let
    Tickets = #table({"Agent", "Opened", "Priority", "Tag"}, {
        {"Ana", #date(2026, 10, 1), "High", "billing"},
        {"Ana", #date(2026, 10, 9), "Low", "login"},
        {"Ana", #date(2026, 10, 4), "High", "billing"},
        {"Ben", #date(2026, 10, 2), "Low", "login"},
        {"Ben", #date(2026, 10, 7), "Low", "refund"}
    }),
    Summary = Table.Group(Tickets, {"Agent"}, {
        {"High priority", each List.Count(List.Select([Priority], each _ = "High")), Int64.Type},
        {"Tags", each Text.Combine(List.Sort(List.Distinct([Tag])), ", "), type text},
        {"First", each List.Min([Opened]), type date},
        {"Last", each List.Max([Opened]), type date},
        {"Most common tag", each List.Mode([Tag]), type text}
    })
in
    Summary

Result

AgentHigh priorityTagsFirstLastMost common tag
Ana2billing, login2026-10-012026-10-09billing
Ben0login, refund2026-10-022026-10-07login

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

Ben's tags are tied (one each); List.Mode returns one of them. When ties matter, define a tie rule instead.
Four sales rows for the North and South regions are grouped by Region into two rows: North with 2 orders totalling 320 and South with 2 orders totalling 130.
Group By turns many rows into one row per key, with the aggregations you choose.

The recipes, one line each

QuestionAggregation
How many rows meet a condition?List.Count(List.Select([Priority], each _ = "High"))
Which distinct values, as text?Text.Combine(List.Sort(List.Distinct([Tag])), ", ")
First / last date?List.Min([Opened]), List.Max([Opened])
Most frequent value?List.Mode([Tag])
How many distinct values?List.Count(List.Distinct([Customer]))
Share of rows meeting a condition?List.Count(List.Select(...)) / Table.RowCount(_)
The whole row with the max value?Table.Max(_, "Amount") (a record; expand it)

Each aggregation is independent, so you can mix them freely in one Group By step. That keeps the grain obvious: one row per agent, with every measure computed over the same group of rows.

For conditional counts, you can also filter before grouping, but then groups with no matching rows disappear entirely. Counting with List.Select inside the aggregation keeps every group and shows 0, as Ben's high-priority count does above.

The row with the latest date

let
    Tickets = #table({"Agent", "Opened", "Tag"}, {{"Ana", #date(2026, 10, 1), "billing"}, {"Ana", #date(2026, 10, 9), "login"}, {"Ben", #date(2026, 10, 7), "refund"}}),
    Latest = Table.Group(Tickets, {"Agent"}, {{"Row", each Table.Max(_, "Opened"), type record}}),
    Expanded = Table.ExpandRecordColumn(Latest, "Row", {"Opened", "Tag"}, {"Latest opened", "Latest tag"})
in
    Expanded

Result

AgentLatest openedLatest tag
Ana2026-10-09login
Ben2026-10-07refund

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

If an aggregation fails for one group (for example List.Mode on a group with only nulls), the error sits in that group's cell. Wrap fragile aggregations in try … otherwise null so one odd group doesn't block the whole summary.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.