Table.Skip - Function | Power Query How

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

Returns a table that does not contain the first specified number of rows, countOrCondition, of the table table. The number of rows skipped depends on the optional parameter countOrCondition.

If countOrCondition is omitted only the first row is skipped.
If countOrCondition is a number, that many rows (starting at the top) will be skipped.
If countOrCondition is a condition, the rows that meet the condition will be skipped until a row does not meet the condition.

Examples

The Table.Skip function is useful in different scenarios. Let’s see how to use it effectively with a few examples.

Skipping the Nth Row

Consider the following dataset, generated by a step called ‘Source’.

Need to skip the first row? You can use the Table.Skip function like this:

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.

Dynamically 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

Suppose you wish to skip all the rows where the ‘Price’ column has a value greater than 25. The Table.Skip function can handle this too:

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

Handling Top Rows with Unnecessary Data

Sometimes, datasets might have extra rows at the top that need to 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 start 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:

Last update: August 28, 2023 | Contribute » | Contributors: Rick de Groot, Mahmoud Bani Asadi
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-skip
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.