Table.FirstN - Table Function

Table.FirstN is a Power Query M function that returns the first row(s) of a table depending on the value of countOrCondition. The function returns the first N rows or rows that meet a specific condition.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Table.FirstN function returns the first N rows from a table. It allows you to specify either a fixed number of rows to retrieve or apply a condition that filters the rows based on specific criteria.

You can take two approaches for the countOrCondition argument:

If countOrCondition is a number, that many rows (starting at the top) will be returned. If the specified number of rows exceeds the total number of rows in the table, the function returns all available rows without error.
If countOrCondition is a condition, the rows that meet the condition will be returned until a row does not meet the condition.

Examples

Suppose we have a table called Source with four columns. Here’s the table structure:

Table.First dataset as start in Power Query M

Returning Rows Based on a Count

To return the first two rows from this table, you can use the following formula:

Table.FirstN( Source, 2 )

This will return the first two rows:

Table.FirstN returns the first two rows of a table in Power Query M

The Table.FirstN( Source, 2 ) function uses a number (in this case, 2) to specify how many rows you want to return. If the number of rows you specify exceeds the total number of rows in the table, it will simply return all available rows without causing an error.

For example, if you request more rows than the table contains:

Table.FirstN( Source, 12 )

This will return all 8 rows, as the table has only 8 rows.

Returning Rows Based on a Condition

You can also use Table.FirstN with a condition to filter the rows. For instance, if you only want to return the first rows where the value in the [TimesBorrowed] column is less than 60, you can use the following syntax:

Table.FirstN( Source, each [TimesBorrowed] < 60 )

This will return the first three rows of the table because row four has a value of 60, which does not meet the condition.

Table.FirstN returns rows that meet a condition in Power Query M

In this example, we pass a condition to Table.FirstN instead of providing a number. The function stops returning rows once it encounters a row that does not meet the condition—in this case, [TimesBorrowed] < 60.

Full Code Example

Here’s the full Power Query M code for both of the examples mentioned:

let
  Source = 
    #table(
      type table [ Index = Int64.Type, Title = text, Genre = text, TimesBorrowed = Int64.Type ],
      {
          { 1, "1984",                                  "Dystopian", 30 },
          { 2, "To Kill a Mockingbird",                 "Fiction",   50 },
          { 3, "The Great Gatsby",                      "Fiction",   45 },
          { 4, "Harry Potter and the Sorcerer's Stone", "Fantasy",   60 },
          { 5, "The Hobbit",                            "Fantasy",   35 },
          { 6, "Pride and Prejudice",                   "Romance",   40 },
          { 7, "The Catcher in the Rye",                "Fiction",   20 },
          { 8, "The Shining",                           "Horror",    25 }
      }
    ),
    TableFirstN_WithCount = Table.FirstN( Source, 2 ),
    TableFirstN_WithCondition = Table.FirstN( Source, each [TimesBorrowed] < 60 )
in
    TableFirstN_WithCondition

Other functions related to Table.FirstN are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-firstn