Downloads
Power Query MTable: rowsBeginner

Table.RemoveRows

Removes a block of rows by position, starting at the row position you give.

Syntax
Table.RemoveRows(table as table, offset as number, optional count as nullable number) as table

Returns: A table without the removed rows.

What it does

Table.RemoveRows deletes rows by where they sit in the table. Positions start at 0, so the first row is 0. It is handy for removing a fixed block such as a title area, but a position is fragile, so use it only when the layout never changes.

Syntax and arguments

Example

A trial balance export has four title rows at the top, then the real data. The first four positions (0 to 3) are removed.

let
    Source = Excel.CurrentWorkbook(){[Name = "TrialBalance"]}[Content],
    NoTitle = Table.RemoveRows(Source, 0, 4),
    Promoted = Table.PromoteHeaders(NoTitle, [PromoteAllScalars = true])
in
    Promoted

The first step removes the title rows. The second uses the first remaining row as the column headers. This works only while the export always has exactly four title rows.

Example: a second, different use

A monthly report has a two-row footnote block in the middle of the data, at positions 12 and 13. The block is removed by position.

let
    Source = Excel.CurrentWorkbook(){[Name = "MonthlyReport"]}[Content],
    NoFootnote = Table.RemoveRows(Source, 12, 2)
in
    NoFootnote

This is risky. If the report changes by one row, the wrong rows are deleted without any error. A text test with Table.SelectRows is safer when the rows have a recognizable label.

Things to watch

In business terms

Accounting exports often arrive with a preamble of company name, date range and blank lines, or with footnotes at the end. Removing these rows is the first cleaning step for many month-end files, but it should be done with care and checked each month.

Related functions

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