Table.LastN Function (Power Query M)

Table.LastN returns the last row or rows from a table, either a fixed count or a trailing run that meets a condition. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to grab the bottom rows of a table, like the last few invoices or the lowest values after a sort, this is the function for the job.

Syntax of Table.LastN Function

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

where

• table (required, table). The table you want to take rows from.
• countOrCondition (required, any). Either a number of rows to keep from the bottom, or a condition. With a condition, it keeps the trailing rows that match and stops at the first row from the bottom that does not.

Returns: a table containing the last rows of the input. With a count, you get that many rows from the end. With a condition, you get the unbroken run of matching rows at the bottom.

In plain terms, you point it at a table and tell it how many rows to take from the bottom, or give it a rule for which trailing rows to keep.

Example 1: Keep the last 2 rows with a count

You have an Invoices query and want only the two most recent rows at the bottom.

Here is the starting data:

InvoiceNo	Client	Amount
INV-9001	Northwind	540
INV-9002	Fabrikam	275
INV-9003	Litware	880
INV-9004	Proseware	410
INV-9005	Tailspin	615

Pass 2 as the count:

let
    Source = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],
    Result = Table.LastN(Source,2)
in
    Result

The result keeps the bottom two rows:

InvoiceNo	Client	Amount
INV-9004	Proseware	410
INV-9005	Tailspin	615

“Last” goes by the table’s current order, so you get the rows exactly as they sit at the bottom.

Example 2: Keep the trailing run that matches a condition

Pass a condition instead of a number and Table.LastN scans from the bottom up, keeping rows until it hits one that fails.

Here is the starting data:

Sensor	Level
S-A	12
S-B	3
S-C	9
S-D	7
S-E	8

Keep the trailing rows where Level is 5 or more:

let
    Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Level",Int64.Type}}),
    Result = Table.LastN(Typed,each [Level]>=5)
in
    Result

It keeps S-C, S-D, and S-E:

Sensor	Level
S-C	9
S-D	7
S-E	8

S-A has a Level of 12, which matches, but S-B (3) breaks the run going up, so S-A is excluded.

Example 3: How this differs from Table.SelectRows

Table.SelectRows looks like a close cousin, but it keeps every matching row anywhere in the table, not just the trailing run.

The data is the same as Example 2:

Sensor	Level
S-A	12
S-B	3
S-C	9
S-D	7
S-E	8

Run the same Level>=5 test through Table.SelectRows:

let
    Source = Excel.CurrentWorkbook(){[Name="Readings2"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Level",Int64.Type}}),
    Result = Table.SelectRows(Typed,each [Level]>=5)
in
    Result

Now S-A is back in the result:

Sensor	Level
S-A	12
S-C	9
S-D	7
S-E	8

Use Table.SelectRows when you want all matches, and Table.LastN when you only want the unbroken run at the bottom.

Example 4: Get the bottom 3 rows after sorting

Sort first with Table.Sort, then take from the bottom, and you have a quick “bottom N” pattern. Here we want the three slowest marathon finishes.

Here is the starting data:

Runner	FinishMin
Devi	188
Marco	142
Lena	205
Omar	167
Priya	159

Sort by FinishMin ascending, then keep the last 3:

let
    Source = Excel.CurrentWorkbook(){[Name="Marathons"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"FinishMin",Int64.Type}}),
    Sorted = Table.Sort(Typed,{{"FinishMin",Order.Ascending}}),
    Result = Table.LastN(Sorted,3)
in
    Result

The bottom three after the sort are the three highest times:

Runner	FinishMin
Omar	167
Devi	188
Lena	205

Sorting ascending puts the largest values at the bottom, so Table.LastN returns the slowest finishers.

Example 5: A count larger than the table returns all rows

If the count is bigger than the number of rows, you get the whole table back, with no error.

Here is the starting data:

Plot	Trees
Orchard North	36
Orchard East	21
Orchard South	44

Ask for 10 rows from a 3-row table:

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

You get all three rows:

Plot	Trees
Orchard North	36
Orchard East	21
Orchard South	44

This makes it safe to use on tables whose row count you do not know up front.

Example 6: A count of 0 returns an empty table

Passing 0 returns a table with the same columns but no rows. Here we wrap it in Table.RowCount to show the result is empty.

let
    Source = Table.LastN(#table({"Item","Qty"},{{"Bolts",40},{"Nuts",75}}),0),
    Result = Table.RowCount(Source)
in
    Result

Result: 0

The empty table still carries the Item and Qty columns, so downstream steps that reference those columns will not break.

Things to keep in mind with Table.LastN

• It works on the table’s current order, not on values. “Last” is positional. Sort first (Example 4) if you want the last rows by some value.
• The condition keeps only the bottom run, not all matches. It stops at the first row from the bottom that fails the test, so an earlier matching row is dropped (Example 2). For all matches, use Table.SelectRows.
• Compare numbers as numbers. Columns loaded from Excel come in as type any, so set the type with Table.TransformColumnTypes(...,Int64.Type) before a >= test, or text comparison can give odd results.
• A count above the row total is safe. It returns the whole table instead of throwing (Example 5).
• A negative count throws. Table.LastN(t,-1) raises Expression.Error: The argument to function 'Table.LastN' must be greater than or equal to 0. Use 0 for an empty result.

Common questions about Table.LastN

What is the difference between Table.LastN and Table.Last?

Table.Last returns a single row as a record, the very last one. Table.LastN returns a table of one or more rows, so it is what you want when you need more than the final row or want the result to stay a table.

How do I drop the last few rows instead of keeping them?

Use Table.RemoveLastN, which is the mirror image. Table.LastN(t,3) keeps the bottom three rows, while Table.RemoveLastN(t,3) removes them and keeps everything above.

List of All Power Query Functions

Related Power Query Functions / Articles:

• Table.RemoveLastN Function
• List.LastN Function
• Table.FirstN Function