Table.AlternateRows - Table Function

Table.AlternateRows is a Power Query M function that keeps an initial offset and then alternates taking and skipping rows based on specified parameters. The function returns a new table with the filtered rows.

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

Syntax

Table.AlternateRows(
   table as table,
   offset as number,
   skip as number,
   take as number,
) as table

Argument	Description
table	The table to start.
offset	The number of rows to keep before starting the extraction pattern.
skip	The number of rows to remove with each iteration.
take	The number of rows to keep with each iteration.

Description

The Table.AlternateRows function retrieves a specified pattern of rows from a table. The function has arguments that allow you to specify parameters for the offset, skip and take which it will use to extract rows. This makes it possible to return specific rows based on the pattern you define.

Examples

Let’s explore a few examples using this function. We’ll begin by using the following table, referred to as Source:

Table.First dataset as start in Power Query M

Example 1: Returning Every Second Row

To return every second row from the table, we can use the following expression:

Table.AlternateRows( Source, 0, 1, 1 )

Here’s what the outcome looks like:

Table.AlternateRows return each second row in Power Query M

Example 2: Keeping the First Row, Then Skipping Each Second Row

To keep the first row and then skip every second row in the table, we can use an offset of 1. The offset tells Power Query how many rows to keep before starting the alternating pattern.

Table.AlternateRows( Source, 1, 1, 1 )

Here’s the resulting table:

Table.AlternateRows skip each second row in Power Query M

Example 3: Skipping Two Rows, Then Keeping One

In this case, we’ll return a pattern where we keep the first row, and then iterate over the entire table by skipping two rows, keeping 1 row, and then repeat.

Table.AlternateRows( Source, 1, 2, 1 )

Here’s what that looks like:

Table.AlternateRows keep a row and skip two in Power Query M

Example 4: Skipping Two Rows, Then Keeping Two Rows

In this final example, we’ll use a pattern that keeps the first row and then follows a pattern of skipping two rows and keeping two rows:

Table.AlternateRows( Source, 1, 2, 2 )

This is what the resulting table looks like:

Table.AlternateRows keep two rows and skip two rows in Power Query M

Full M Code

To test out these examples in your own Power Query environment, paste the following code into the Advanced Editor:

let
  Source = 
    #table(
      type table [ Index = Int64.Type, Title = text, Genre = text, TimesBorrowed = Int64.Type ],
      {
          { 1, "1984",                                  "Dystopian", 30 },
          { 2, "To Kill a Mockingbird",                 "Fiction",   50 },
          { 3, "The Great Gatsby",                      "Fiction",   45 },
          { 4, "Harry Potter and the Sorcerer's Stone", "Fantasy",   60 },
          { 5, "The Hobbit",                            "Fantasy",   35 },
          { 6, "Pride and Prejudice",                   "Romance",   40 },
          { 7, "The Catcher in the Rye",                "Fiction",   20 },
          { 8, "The Shining",                           "Horror",    25 }
      }
    ),
    SkipEachFirstRow = Table.AlternateRows( Source, 0, 1, 1 ),
    SkipEachSecondRow = Table.AlternateRows( Source, 1, 1, 1 ),
    SkipTwo_KeepOne = Table.AlternateRows( Source, 1, 2, 1 ),
    SkipTwo_KeepTwo = Table.AlternateRows( Source, 1, 2, 2 )
in
    SkipTwo_KeepTwo

Other functions related to Table.AlternateRows are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-alternaterows