Table.RemoveLastN Function in Power Query M (5 Easy Examples)

Table.RemoveLastN returns a table with the last rows removed, either a fixed number of them or as many trailing rows as meet a condition. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to strip a trailing total, subtotal, or footer row off the bottom of a query, this is the function you reach for. You give it the table and tell it how many rows to drop, or a rule for which trailing rows to drop.

Table of Contents

Syntax of Table.RemoveLastN Function

Table.RemoveLastN(table as table, optional countOrCondition as any) as table

where

table (required, table). The table to remove rows from.
countOrCondition (optional, any). How many trailing rows to remove, or a condition that picks them. Omit it to remove only the last row. Pass a number to remove that many rows from the bottom. Pass a condition like each [Col]="x" to remove trailing rows until one fails the test.

Returns: a table with the bottom rows removed. The original row order is kept, and if you remove more rows than the table has, you get an empty table.

In plain terms, it works from the bottom up. It chops off the last row by default, a set number of rows if you pass a count, or a run of trailing rows if you pass a condition.

Example 1: Remove only the last row

Say you have a MonthlySales query that ends with a Total row you do not want.

You want everything except that final row.

Here is the starting data:

Month	Units
Jan	420
Feb	380
Mar	510
Total	1310

Call Table.RemoveLastN with no second argument:

let
    Source = Excel.CurrentWorkbook(){[Name="MonthlySales"]}[Content],
    Result = Table.RemoveLastN(Source)
in
    Result

With the count omitted, it drops just the last row.

The result keeps the three month rows:

Month	Units
Jan	420
Feb	380
Mar	510

The Total row is gone and the monthly rows stay in order.

Example 2: Remove a fixed number of rows

This time the table ends with two rows you want gone, a Subtotal and a Grand Total.

Here is the starting data:

Day	Visits
Mon	120
Tue	95
Wed	140
Subtotal	355
Grand Total	355

Pass 2 to remove the last two rows:

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

The number tells it how many rows to take off the bottom.

The result keeps only the three day rows:

Day	Visits
Mon	120
Tue	95
Wed	140

Example 3: Drop a trailing total row by condition

A count works when you know how many rows to remove. When you do not, a condition matches them instead.

Here you want to drop the Total row without counting, by testing the Region value.

Here is the starting data:

Region	Revenue
North	8200
South	6450
East	7100
West	5900
Total	27650

Pass a condition that matches the total row:

let
    Source = Excel.CurrentWorkbook(){[Name="RegionRevenue"]}[Content],
    Result = Table.RemoveLastN(Source,each [Region]="Total")
in
    Result

It removes trailing rows where Region is Total, working up from the bottom.

The result keeps the four region rows:

Region	Revenue
North	8200
South	6450
East	7100
West	5900

This is handy when imported data ends with a summary row but the row count is not fixed.

Example 4: A condition stops at the first non-match

The condition only removes a continuous run of trailing rows. The moment a row from the bottom fails the test, it stops, even if matching rows sit further up.

Here you want to strip two Footer rows off the end of an import.

Here is the starting data:

Item	Amount
Widget	45
Gadget	60
Gizmo	30
Footer-Generated	0
Footer-PageBreak	0

Match any item that starts with Footer:

let
    Source = Excel.CurrentWorkbook(){[Name="ImportFooter"]}[Content],
    Result = Table.RemoveLastN(Source,each Text.StartsWith([Item],"Footer"))
in
    Result

It removes the two footer rows, then hits Gizmo, which fails the test, so it stops.

The result keeps the three product rows:

Item	Amount
Widget	45
Gadget	60
Gizmo	30

Example 5: Removing more rows than exist returns an empty table

If the count is larger than the number of rows, you do not get an error. You get an empty table.

Here TinyList has only two rows, and you ask to remove 10. To show the outcome as a number, wrap the result in Table.RowCount:

let
    Source = Excel.CurrentWorkbook(){[Name="TinyList"]}[Content],
    Trimmed = Table.RemoveLastN(Source,10),
    Result = Table.RowCount(Trimmed)
in
    Result

Result: 0

The function removes every row and stops, so the row count is 0 rather than an error.

Things to keep in mind with Table.RemoveLastN

A condition removes a trailing run only, not every match. It works up from the bottom and stops at the first row that fails the test (Example 4). A matching row higher up survives, so this is not a filter. Use Table.SelectRows to remove matches anywhere.
Row order decides the result. Because it counts from the bottom, sort the table the way you expect before calling it. The same data in a different order removes different rows.
It removes rows, never columns. To drop columns use Table.SelectColumns or Table.RemoveColumns, and to remove rows from the top use Table.RemoveFirstN.
A negative or non-numeric count throws. Table.RemoveLastN(Source,-1) raises Expression.Error: The argument to function 'Table.RemoveLastN' must be greater than or equal to 0. Pass a count of 0 or more.

Common questions about Table.RemoveLastN

What is the difference between Table.RemoveLastN and Table.RemoveFirstN?

Table.RemoveLastN removes rows from the bottom of the table, while Table.RemoveFirstN removes them from the top. Both take the same count-or-condition argument.

How is it different from Table.SelectRows or a filter?

A filter removes every row that meets a condition, anywhere in the table. Table.RemoveLastN with a condition only removes a continuous run at the very bottom and stops at the first non-match.

List of All Power Query Functions

Related Power Query Functions / Articles: