Table.RemoveFirstN
Removes the first rows, by a count or until a test stops being true.
Table.RemoveFirstN(table as table, optional countOrCondition as any) as tableReturns: A table without the first rows.
What it does
Table.RemoveFirstN drops the rows at the top of a table and keeps the rest. Accounting exports often begin with a company name, a report title and some blank lines. This step removes that preamble so the real header row can become the column names.
Syntax and arguments
- table: the table to change. Required.
- countOrCondition: either a number of rows to remove, or a function. If you leave it out, one row is removed. With a function, rows are removed from the top while the function returns true, and the first row where it is false stops the removal.
Example
A bank export starts with four rows of report information. The first four rows are removed, and the next row becomes the header.
let
Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
NoPreamble = Table.RemoveFirstN(Source, 4),
Headers = Table.PromoteHeaders(NoPreamble, [PromoteAllScalars = true])
in
Headers
The count is the number of rows, not a column value. Check the top of the file before you choose the number. A number that is too high also removes real transactions.
Example: a second, different use
When the number of title rows can change from month to month, use a condition. Rows are removed from the top while the Date column is blank.
let
Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
NoPreamble = Table.RemoveFirstN(Source, each [Date] = null)
in
NoPreamble
Things to watch
- A count is fixed. A condition adapts to a changing layout, but it stops at the first row that fails the test, so check that the test is written the way you intend.
- Remove the preamble before promoting headers, or the title text becomes a column name.
- Query folding: check View Native Query on the step. Counted removal can fold on a database source, and a condition often does not.
- Keep the top of the raw file in your records. A removed row is no longer in the report.
In business terms
Bank statements, aged trial balances and payroll registers all come with headings above the data. Removing the preamble consistently is the first step in building a file that can be refreshed each month without manual editing.