Downloads
Power Query MTable: rowsBeginner

Table.RemoveLastN

Removes the last rows, by a count or while a test holds from the bottom up.

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

Returns: A table without the last rows.

What it does

Table.RemoveLastN removes rows from the bottom of a table. Reports often end with a grand total, a line of notes or a signature block. Those rows must go before you add up a column, or the total is counted twice.

Syntax and arguments

Example

A general ledger export ends with a total line and some notes. Those lines have a blank Account column. The condition removes trailing rows where Account is blank.

let
    Source = Excel.CurrentWorkbook(){[Name = "GLExport"]}[Content],
    Postings = Table.RemoveLastN(Source, each [Account] = null)
in
    Postings

Each trailing row with a blank Account is removed, and the removal stops at the first row that has an account. The posting rows above it are kept.

Example: a second, different use

A fixed footer of two note lines is always present at the end of a report. The count removes exactly those two rows.

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

Things to watch

In business terms

Totals and notes at the bottom of a report are the most common cause of double counting in spreadsheets. Removing them in the query, and checking the result against the report's own total, makes the control visible to the reviewer.

Related functions

All Power Query M functions · View as Markdown · M basics