Downloads
Power Query MTable: rowsBeginner

Table.FirstN

Keeps the first rows, either a set number or while a condition holds.

Syntax
Table.FirstN(table as table, countOrCondition as any) as table

Returns: A table with the first rows.

What it does

Table.FirstN keeps the top rows of a table. Used on its own it gives you a sample to check the layout. Used after a sort, it gives you a top list, such as the ten largest customers by credit limit. The sort is what gives the rows meaning, because the database does not promise any order unless you ask for one.

Syntax and arguments

Example

A credit committee wants the ten customers with the highest credit limits in the sample database.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Customers = Source{[Schema = "classicmodels", Item = "customers"]}[Data],
    Ranked = Table.Sort(Customers, {{"creditLimit", Order.Descending}}),
    Top10 = Table.FirstN(Ranked, 10)
in
    Top10

The table is sorted from the highest limit down, and the first ten rows are kept. Both steps are simple enough that Power Query can send them to the database as one query.

Example: a second, different use

A transaction list is sorted by date. The first run of rows that share a clean date is kept, and the list stops at the first blank date.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Clean = Table.FirstN(Source, each [Date] <> null)
in
    Clean

Things to watch

In business terms

Top-ten lists for credit reviews, largest vendors for a spend review and the first lines of an export to check the layout are typical uses in finance work.

Related functions

All Power Query M functions · View as Markdown · M basics