Table.Range - Table Function

Table.Range is a Power Query M function that retrieves rows from a table starting at a specified offset. The function returns a new table containing the selected rows, with an optional count parameter to specify how many rows to return.

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

Syntax

Table.Range(
   table as table,
   offset as number,
   optional count as nullable number,
) as table

Description

The Table.Range function extracts a subset of rows from a table, starting after skipping a specified number of rows (the offset). You can use it in two ways:

Return all rows after the offset: Provide the number of rows to skip (offset), and it will return all rows from that point onward.
Return a specific number of rows: Provide both the offset and the number of rows to retrieve, and it will return only that portion of the table.

Examples

Let’s go through a couple of examples to show how the Table.Range function works.

Returning Rows Starting at a Specific Position

In this example, we have a table of books, each with details like the number of times it has been borrowed. Here’s the table:

Table.First dataset as start in Power Query M

To return all rows after the fourth row you can specify:

Table.Range( Source, 4 )

This tells Power Query to skip the first 4 rows and then return all remaining rows:

Table.Range returns all rows after skipping four in Power Query M

Selecting a Specific Number of Rows from a Position

In the next example, we want to select two rows after skipping the first 3 rows. Here’s the function:

Table.Range( Source, 3, 2 )

This removes two rows starting from the 4th row, which are "Harry Potter and the Sorcerer's Stone" and "The Hobbit". The result will be:

Table.Range returns two rows after skipping three in Power Query M

Full M code

To try the M code yourself, you can 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 }
      }
    ),
    TableRange_WithoutCount = Table.Range( Source, 4 ),
    TableRange_WithCount = Table.Range( Source, 3, 2 )
in
    TableRange_WithCount

Other functions related to Table.Range are:

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