Table.RemoveRows
Removes a block of rows by position, starting at the row position you give.
Table.RemoveRows(table as table, offset as number, optional count as nullable number) as tableReturns: 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
- table: the table to change. Required.
- offset: the position of the first row to remove. The first row is 0. Required.
- count: how many rows to remove, starting at offset. If you leave it out, one row is removed.
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
- Positions are zero-based. Position 0 is the first row, not the second.
- A fixed position breaks when the export changes. Prefer removing rows by their content.
- Query folding: positions are not part of a database query in a reliable way, so this step usually does not fold.
- Count is optional. Leaving it out removes one row, which is a common surprise.
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.