Table.Skip - Table Function

Table.Skip is a Power Query M function that returns a table without the first specified number of rows, depending on the optional countOrCondition parameter. The function returns a table with the specified rows skipped.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.Skip(
   table as table,
   optional countOrCondition as any,
) as table

Description

The Table.Skip function returns a table that excludes a specified number of rows or rows that meet a condition. This is particularly useful when you need to remove unnecessary data from the top of your table.

The behavior of Table.Skip depends on the value provided for the optional countOrCondition parameter:

When countOrCondition is omitted, the function skips only the first row by default.
When countOrCondition is a number, the function skips that many rows, starting from the top of the table.
When countOrCondition is a condition, the function skips rows that meet the condition until it encounters the first row that does not satisfy it.

Note that the Table.RemoveFirstN function behaves identical to the Table.Skip function, they’re syntax sugar for the same operation.

Examples

The Table.Skip function can be applied in various scenarios. Below are some examples demonstrating how to use it effectively.

Skipping the Nth Row

Suppose you have a dataset called 'Source'.

To skip the first row, you can use the following expression:

1. Table.Skip to skip the first row in Power Query M

This command will result in skipping the initial row of the table. If you omit the second argument, that would result in the same operation of skipping 1 row. You can change the number of rows to skip to any number you wish.

Skipping Rows Based on Condition

Now, let’s consider a different scenario with another dataset, also named ‘Source’.

Dataset for Table.Skip Dynamic example 2

For example, suppose you want to skip all rows where the ‘Price’ column has a value greater than 25. You can achieve this by using:

2. Table.Skip to skip all rows bigger than 25 in Power Query M

Removing Unnecessary Rows Above Headers

Sometimes, datasets might have extra rows at the top that must be removed.

Dataset for Table.Skip removing top rows for Income statement

To effectively remove the rows that lay above the table headers we need to know where the table headers start. In this case, the table in Column1 starts with the text “GL Code”. To effectively remove all unnecessary rows above you can use:

3. Table.Skip to skip all rows unequal to String

Want to see these examples in action? The following code illustrates all the examples from this page. Simply paste it into Power Query’s advanced editor:

let
  Source1 = 
    #table(
      type table[CustomerID = Int64.Type, Name = text, Phone = text],
      {
        {1, "Bob",    "123-4567"},
        {2, "Jim",    "987-6543"},
        {3, "Paul",   "543-7890"},
        {4, "Ringo",  "232-1550"}
      }
  ),
  SkipFirstRow = Table.Skip( Source1, 1 ),
  Source2 = 
    #table(
      type table [OrderID = Int64.Type, CustomerID = Int64.Type, Item = text, Price = number], 
      {
        {1, 1, "Fishing rod", 100.0}, 
        {2, 1, "1 lb. worms", 55.0 }, 
        {3, 2, "Fishing net", 36.0 }, 
        {4, 3, "Fish tazer",  20.0 }, 
        {5, 3, "Bandaids",    2.0  }, 
        {6, 1, "Tackle box",  20.0 }, 
        {7, 5, "Bait",        3.25 }, 
        {8, 5, "Fishing Rod", 100.0}, 
        {9, 6, "Bait",        3.25 }
      }
    ),
  SkipRowsBiggerThan25 = Table.Skip( Source2, each [Price] > 25 ),
    Source3 = 
      #table(
        type table [Column1 = text, Column2 = text, Column3 = number], 
        {
          { null,               null,           null    }, 
          { "Income Statement", null,           null    }, 
          { "GL Code",          "Description", "Amount" }, 
          { 8000,               "Sales",        4000    },
          { 8010,               "Sales",        250     },
          { 8015,               "Sales",        3499    }
        }
      ),
  SkipJunkRows = Table.Skip(Source3, each [Column1] <> "GL Code")
in
  SkipJunkRows

Other functions related to Table.Skip are:

Contribute » | Contributors: Rick de Groot, Mahmoud Bani Asadi
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-skip