Table.RemoveLastN
Removes the last rows, by a count or while a test holds from the bottom up.
Table.RemoveLastN(table as table, optional countOrCondition as any) as tableReturns: 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
- table: the table to change. Required.
- countOrCondition: a number of rows to remove from the bottom, or a function. With a function, rows are removed from the bottom while the function returns true. If you leave it out, one row is removed.
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
- Keep a copy of the grand total before you remove it. A total row is useful for a control check: compare your sum of the posting rows with the export's total.
- A count is fixed. If the footer length changes, use a condition instead.
- Sort order matters. "Last" means the last row in the current order, so sort first if the source order is not reliable.
- Query folding: this step usually does not fold in the same way as the first-row version. Check View Native Query.
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.