Table.FirstN Function in Power Query M (6 Easy Examples)

Table.FirstN returns the first rows of a table, either a fixed number of rows or every row until a condition first fails. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to keep only the top rows of a table, or grab rows from the top while some condition holds, this is the function to reach for.

Table of Contents

Syntax of Table.FirstN Function

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

where

table (required, table). The table to take rows from.
countOrCondition (required, any). The any type covers two overloads. Pass a number to get that many rows from the top, or pass a condition (an each function) to get rows from the top until the first row that fails it.

Returns: a table containing the first row(s) of table. With a count of 0 it returns an empty table, and a count larger than the row count returns the whole table without an error.

In plain terms, you give it a table and either a row count or a condition, and it hands back rows from the top.

Example 1: Keep the first 3 rows with a count

Say you have a Shipments query and you only want the first three shipments.

Here is the starting data:

ShipmentID	Destination	Boxes
SHP-201	Aurora	12
SHP-202	Bellevue	8
SHP-203	Casper	15
SHP-204	Dover	6
SHP-205	Eugene	9

Pass the table and a count of 3:

let
    Source = Excel.CurrentWorkbook(){[Name="Shipments"]}[Content],
    Result = Table.FirstN(Source,3)
in
    Result

The result keeps the top three rows in their original order:

ShipmentID	Destination	Boxes
SHP-201	Aurora	12
SHP-202	Bellevue	8
SHP-203	Casper	15

The count form is purely positional. It takes rows from the top, nothing else.

Example 2: Use a condition that stops at the first non-match

This is the overload that surprises people. With a condition, Table.FirstN returns rows from the top only while the condition keeps passing.

Say you have an Inspections query and you want the opening streak of defect-free units.

Here is the starting data:

Unit	Defects
U-101	0
U-102	0
U-103	2
U-104	0
U-105	0

Pass an each condition instead of a number:

let
    Source = Excel.CurrentWorkbook(){[Name="Inspections"]}[Content],
    Result = Table.FirstN(Source,each [Defects]=0)
in
    Result

The result has only the first two rows:

Unit	Defects
U-101	0
U-102	0

Row 3 (U-103) fails the condition, so the function stops there for good. Rows 4 and 5 also have zero defects, but they never get checked.

Example 3: Get every match with Table.SelectRows instead

If what you actually want is every row that meets the condition, no matter where it sits, use Table.SelectRows. Here is the same inspections data with the same condition.

Here is the starting data:

Unit	Defects
U-101	0
U-102	0
U-103	2
U-104	0
U-105	0

This time, filter with Table.SelectRows:

let
    Source = Excel.CurrentWorkbook(){[Name="Inspections2"]}[Content],
    Result = Table.SelectRows(Source,each [Defects]=0)
in
    Result

The result has all four matching rows:

Unit	Defects
U-101	0
U-102	0
U-104	0
U-105	0

Same data, same condition, different result. Table.FirstN answers “how long does the streak at the top last”, while Table.SelectRows answers “which rows match anywhere”.

Example 4: Get the top 3 rows after sorting

Because the count form is positional, the real-world “top N by value” recipe is sort first, then take the first N.

Say you want the three best-attended gym classes.

Here is the starting data:

Class	Attendees
Spin	42
Yoga	35
Boxing	51
Pilates	28
Zumba	47

Sort descending, then keep the first three rows:

let
    Source = Excel.CurrentWorkbook(){[Name="ClassAttendance"]}[Content],
    Sorted = Table.Sort(Source,{{"Attendees",Order.Descending}}),
    Result = Table.FirstN(Sorted,3)
in
    Result

The result is the top three classes by attendance:

Class	Attendees
Boxing	51
Zumba	47
Spin	42

The Table.Sort step does the ranking. Table.FirstN just trims the sorted table to three rows.

Example 5: Ask for more rows than the table has

Requesting more rows than exist is safe. You get the whole table back, with no padding and no error.

Here is the starting data, a 4-row Cafes table:

Cafe	Seats
Brewed Awakening	24
The Daily Grind	18
Mocha Mill	30
Steam & Bean	12

Ask for ten rows:

let
    Source = Excel.CurrentWorkbook(){[Name="Cafes"]}[Content],
    Result = Table.FirstN(Source,10)
in
    Result

The result is simply all four rows:

Cafe	Seats
Brewed Awakening	24
The Daily Grind	18
Mocha Mill	30
Steam & Bean	12

That makes Table.FirstN handy as a row cap. A query that says “give me up to 10 rows” never breaks when the source runs short.

Example 6: Pass zero to get an empty table

A count of 0 returns an empty table. Here Table.RowCount confirms it on a small inline table.

let
    Source = Table.FirstN(#table({"Item","Qty"},{{"Pens",10},{"Pads",5}}),0),
    Result = Table.RowCount(Source)
in
    Result

Result: 0

The empty result still keeps the Item and Qty columns, so later steps that reference them keep working.

Things to keep in mind with Table.FirstN

A condition the very first row fails returns an empty table. Zero rows, no error. If you expected matches and got nothing, check what the top row looks like.
The condition sees the whole row as a record. You can reference any column, or combine several, like each [Defects]=0 and [Boxes]>5.
The UI writes this function for you. Home > Reduce Rows > Keep Rows > Keep Top Rows generates a Table.FirstN step, so you’ll run into it in auto-generated code too.
Pick the right sibling. Table.First returns only the first row, as a record rather than a table. Table.LastN takes rows from the bottom, and Table.Range grabs a window starting at any offset.

Performance and query folding

With a numeric count against a foldable source like SQL Server, Table.FirstN typically folds into a TOP N query, so only those rows leave the server. The condition form generally does not fold and is evaluated locally.

Even locally it’s cheap. The function streams and can stop reading once it has the rows it needs, so it stays fast on large files.

Common questions about Table.FirstN

What is the difference between Table.FirstN and Table.Skip?

They’re complements. Table.FirstN(Source,3) keeps the first three rows, while Table.Skip(Source,3) removes them and keeps everything after. Table.Skip also accepts a condition, with the same stop-at-first-non-match behavior.

Can I use Table.FirstN to get the first values of a column?

No, it always returns rows as a table. For the first values of a single column, use List.FirstN on the column list, for example List.FirstN(Source[Boxes],3).

List of All Power Query Functions

Related Power Query Functions / Articles: