Table.Skip
Drops the first rows, either a set number or while a condition holds, and keeps the rest.
Table.Skip(table as table, countOrCondition as any) as tableReturns: A table without the skipped rows.
What it does
Table.Skip removes rows from the top of a table. It does the same job as Table.RemoveFirstN, so pick whichever name reads better to you. It is commonly used to skip a title block before the headers, or to step past rows you have already loaded when you read a source in pages.
Syntax and arguments
- table: the table to change. Required.
- countOrCondition: a number of rows to skip, or a function. With a function, rows are skipped from the top while the function returns true, and the first row that fails stops the skipping. Required.
Example
A bank export has two title rows above the headers. The title rows are skipped, and the next row becomes the column names.
let
Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
Body = Table.Skip(Source, 2),
Headers = Table.PromoteHeaders(Body, [PromoteAllScalars = true])
in
Headers
After the skip, the header row is the first row of the table, so promoting headers gives the columns their real names.
Example: a second, different use
A sorted list of invoices is read in pages. The first page keeps the first 100 rows, and the second page skips those 100 and keeps the next 100.
let
Source = Excel.CurrentWorkbook(){[Name = "Invoices"]}[Content],
Sorted = Table.Sort(Source, {{"InvoiceNumber", Order.Ascending}}),
SecondPage = Table.FirstN(Table.Skip(Sorted, 100), 100)
in
SecondPage
Things to watch
- Skip works from the top only. To remove rows from the bottom, use Table.RemoveLastN.
- Sort first when the position of a row matters. Skipping an unsorted list skips whatever rows happen to come first.
- A fixed count breaks if the title block changes size. Use a condition when the number of title rows can vary.
- Query folding: check View Native Query. Counted skips can fold on some database sources, but the result depends on the source.
In business terms
Title blocks above exports and paged reading of long lists are the two common reasons to skip rows. In both cases the step should be documented in the query so the next person knows why the rows are missing.