Power QueryExercises

Keep the top N rows after sorting

Rank rows by a calculated value and keep the top N in a refresh-safe way.

~8 min read · beginner · Module 5: Filtering and sorting rows

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

Before this: Sort rows on more than one column

"Top 2 orders by value" is three steps: calculate the value, sort by it descending, then keep the first two rows. Keep Top Rows (Table.FirstN) only looks at the current order, so the sort must come first.

Worked example

let
    Source = #table({"OrderID", "Customer", "Region", "Product", "Qty", "UnitPrice", "Status"}, {
        {"A-101", "Ana Ruiz", "North", "Kite", 2, 12.5, "Complete"},
        {"A-102", "Ben Okafor", "South", "Reel", 6, 4, "Cancelled"},
        {"A-103", "Cleo Park", "North", "Line", 4, 3.5, "Complete"},
        {"A-104", "Dev Shah", "West", "Kite", 1, 12.5, "Complete"}
    }),
    WithValue = Table.AddColumn(Source, "Value", each [Qty] * [UnitPrice], type number),
    Sorted = Table.Sort(WithValue, {{"Value", Order.Descending}, {"OrderID", Order.Ascending}}),
    Top2 = Table.FirstN(Sorted, 2)
in
    Table.SelectColumns(Top2, {"OrderID", "Value"})

Result

OrderIDValue
A-10125
A-10224

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

The type number argument declares the new column's type, so later steps and the data model see a number instead of any. OrderID is a second sort key so that ties always resolve the same way.

Ties

If the second and third orders have the same value, Table.FirstN keeps whichever comes first after sorting. Without a tie-breaker that's arbitrary, and the 'top 2' can change between refreshes with no change in the data. Decide the business rule: keep both tied rows, or break the tie on a second column.

Top N while a condition holds

Table.FirstN also accepts a condition instead of a count: it keeps leading rows while the condition is true and stops at the first row where it isn't. On a sorted table that gives 'all orders worth at least 20':

let
    Source = #table({"OrderID", "Customer", "Region", "Product", "Qty", "UnitPrice", "Status"}, {
        {"A-101", "Ana Ruiz", "North", "Kite", 2, 12.5, "Complete"},
        {"A-102", "Ben Okafor", "South", "Reel", 6, 4, "Cancelled"},
        {"A-103", "Cleo Park", "North", "Line", 4, 3.5, "Complete"},
        {"A-104", "Dev Shah", "West", "Kite", 1, 12.5, "Complete"}
    }),
    WithValue = Table.AddColumn(Source, "Value", each [Qty] * [UnitPrice], type number),
    Sorted = Table.Sort(WithValue, {{"Value", Order.Descending}}),
    Big = Table.FirstN(Sorted, each [Value] >= 20)
in
    Table.SelectColumns(Big, {"OrderID", "Value"})

Result

OrderIDValue
A-10125
A-10224

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

On an unsorted table the condition form stops early at the first small order, which is rarely what you want. For an unordered rule, use Table.SelectRows instead.

Where to find this in Excel

Excel for Windows
Sort first, then Home > Keep Rows > Keep Top Rows
What you see
You enter how many rows to keep; the count applies to the sorted order.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Filter a table by row position (Microsoft Learn); Sort columns (Microsoft Learn).

Common mistakes

  • Keeping the top rows before sorting, which returns whatever happened to be first in the source.
  • Forgetting a tie-breaker, so the top N changes between refreshes.
  • Using the condition form of Table.FirstN on an unsorted table and losing rows after the first non-match.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Home → Keep Rows → Keep Top Rows writes Table.FirstN with a count.
  • In Power BI, a Top N that should respond to report filters belongs in the visual or a DAX measure; Power Query's top N is fixed at refresh time.
  • The simulator supports both the count and the condition forms of Table.FirstN.
Functions in this lesson:Table.AddColumnTable.SortTable.FirstN

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 free, with no account needed.

Practise: Find the three largest orders (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.