Power QueryExercises

Top N per group in Power Query, with ties handled

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

Jump to the practice tasks

Top 2 products per region

Five orders for customers Ana and Bo. Grouping by customer, sorting each group by amount and keeping two rows leaves Ana's orders of 90 and 70 and both of Bo's orders, 80 and 30.
Top N per group: group, sort inside each group, then keep the first N rows of each.
let
    Sales = #table({"Region", "Product", "Units"}, {
        {"North", "Kite", 40}, {"North", "Reel", 25}, {"North", "Line", 25}, {"North", "Hook", 10},
        {"South", "Reel", 30}, {"South", "Kite", 12}
    }),
    Top = Table.Group(Sales, {"Region"}, {{"Rows", each Table.FirstN(Table.Sort(_, {{"Units", Order.Descending}, {"Product", Order.Ascending}}), 2), type table}}),
    Expanded = Table.ExpandTableColumn(Top, "Rows", {"Product", "Units"})
in
    Expanded

Result

RegionProductUnits
NorthKite40
NorthLine25
SouthReel30
SouthKite12

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

Reel and Line tie on 25 in North. The Product sort key decides that Line takes the second place, every time.

Keep everyone who ties

If tied products should share the place, find the Nth-highest value in each group and keep every row at or above it:

let
    Sales = #table({"Region", "Product", "Units"}, {
        {"North", "Kite", 40}, {"North", "Reel", 25}, {"North", "Line", 25}, {"North", "Hook", 10},
        {"South", "Reel", 30}, {"South", "Kite", 12}
    }),
    Top = Table.Group(Sales, {"Region"}, {{"Rows", (g) =>
        let
            Threshold = List.Min(List.MaxN(g[Units], 2))
        in
            Table.SelectRows(g, each [Units] >= Threshold), type table}}),
    Expanded = Table.ExpandTableColumn(Top, "Rows", {"Product", "Units"})
in
    Expanded

Result

RegionProductUnits
NorthKite40
NorthReel25
NorthLine25
SouthReel30
SouthKite12

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

North returns three rows because two products share second place. List.MaxN returns the two largest values; their minimum is the threshold.

List.MaxN takes the N largest values in descending order, so List.Min(List.MaxN(values, 2)) is the second-largest value even when the largest appears twice: for {40, 40, 25} it returns 40, and both 40s are kept. Decide whether that's the rule you want: 'top two values' and 'top two rows' differ exactly when there are ties.

Choose the rule before building

RuleRows per groupUse
Exactly N, tie-breaker decidesAlways N (or fewer)Fixed-size lists, leaderboards with a clear tie rule
N places, ties shareN or more'Top sellers' where excluding a tie would be unfair

The same pattern answers 'bottom N' questions: sort ascending instead of descending, or use List.MinN for the threshold version. It also works with a calculated measure, such as revenue = units × price, as long as you add that column before grouping.

Expanding only the columns you need keeps the result tidy. The group key (Region) is already on the outer table, so don't expand it again from the nested rows.

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. Free tasks need no account.

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.