Table.LastN - Table Function

Table.LastN is a Power Query M function that retrieves the last row(s) from a table based on a count or condition. The function returns a list of rows that meet the specified criteria.

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

Syntax

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

Description

The Table.LastN function returns the last N rows from a table. You can specify a fixed number of rows to retrieve or provide a condition to return rows based on specific criteria.

There are two ways to use the countOrCondition argument:

If countOrCondition is a number, the function will return that many rows starting from the end of the table. If the number exceeds the total number of rows, all available rows are returned without error.
If countOrCondition is a condition, the function returns rows from the bottom of the table, stopping once it encounters a row that doesn’t meet the condition.

Examples

Let’s use the same example table named 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 last two rows of the table, use the following formula:

Table.LastN( Source, 2 )

This returns the last two rows:

Table.FirstN retrieves the last two rows in Power Query M

Here, the Table.LastN( Source, 2 ) function retrieves the last two rows from the table. If the specified number exceeds the total number of rows, all available rows are returned without generating an error.

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

Table.LastN( Source, 12 )

This will return all 8 rows since the table only contains 8 rows.

Returning Rows Based on a Condition

To return the last rows that meet a specific condition, you can pass a condition to Table.LastN. For example, if you want to return the last rows where the [Index] value is bigger than or equal to 6, use this formula:

Table.LastN( Source, each [Index] >= 6 )

This returns the last three rows because rows 6, 7, and 8 have values higher or equal to 6, while row 5 does not meet the condition.

Table.FirstN uses a condition to retrieve Last N rows in Power Query M

In this example, Table.LastN returns rows from the bottom of the table and stops once it encounters a row that doesn’t meet the condition (in this case, [Index] >= 6).

Full Code Example

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

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 }
      }
    ),
    TableLastN_WithCount = Table.LastN( Source, 2 ),
    TableLastN_WithCondition = Table.LastN( Source, each [Index] >= 6 )
in
    TableLastN_WithCondition

Other functions related to Table.LastN are:

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