Table.Range Function in Power Query M (4 Easy Examples)

Table.Range returns a slice of rows from a table, starting at a given position and optionally limited to a set number of rows. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to grab a specific block of rows from a table, like skipping the first few and keeping the rest, or pulling out a window from the middle, Table.Range is the function for that.

Table of Contents

Syntax of Table.Range Function

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

where

table (required, table). The table to take rows from.
offset (required, number). The zero-based position to start at. 0 is the first row, 1 is the second, and so on.
count (optional, nullable number). How many rows to return from the offset. Omit it to return every row after the offset.

Returns: a table containing the selected rows. If offset lands past the last row, it returns an empty table with the same columns.

In plain terms, you tell it where to start and how many rows to take, and it hands back just that block.

Example 1: Skip the first rows and keep the rest

You have a shift roster and want to drop the first two slots, keeping everyone from the third onward.

Here is the starting data:

Slot	Worker
S1	Priya
S2	Marcus
S3	Lena
S4	Diego
S5	Aisha

Start at offset 2 with no count, so it returns all rows from there on:

let
    Source = Excel.CurrentWorkbook(){[Name="ShiftLog"]}[Content],
    Result = Table.Range(Source,2)
in
    Result

Because offset is zero-based, position 2 is the third row.

The result keeps the last three rows:

Slot	Worker
S3	Lena
S4	Diego
S5	Aisha

The first two slots, S1 and S2, are dropped.

Example 2: Take a fixed window of rows

This time you want just two rows, starting from the second one.

Here is the starting data:

Slot	Worker
S1	Priya
S2	Marcus
S3	Lena
S4	Diego
S5	Aisha

Pass 1 for the offset and 2 for the count:

let
    Source = Excel.CurrentWorkbook(){[Name="ShiftLog2"]}[Content],
    Result = Table.Range(Source,1,2)
in
    Result

It starts at the second row and takes two rows.

The result is a tight two-row slice:

Slot	Worker
S2	Marcus
S3	Lena

This is the pattern to reach for when you want a specific block out of the middle.

Example 3: An offset past the end returns nothing

If your offset is larger than the table has rows, you get back an empty table rather than an error.

Here the source has only three rows, but you ask to start at offset 5. Wrapping the call in Table.RowCount (the table-level cousin of List.Count) counts the rows that come back:

let
    Source = Excel.CurrentWorkbook(){[Name="ShiftLog3"]}[Content],
    Result = Table.RowCount(Table.Range(Source,5))
in
    Result

Result: 0

There is nothing at or after offset 5, so the range is empty and the count is 0.

Example 4: Skip junk header rows and grab the readings

Raw exports often start with a few junk lines before the real data. Table.Range can slice straight to the rows you want.

Here is the starting data, loaded with generic Column1 and Column2 headers:

Column1	Column2
Sensor Export v3	generated by logger
Site: North Field	Quarter: Q2
Reading	Status
temp-18C	OK
temp-19C	OK
temp-56C	HIGH
temp-20C	OK
End of file	—

The four reading rows start at offset 3 (the title, label, and the embedded header sit at offsets 0, 1, and 2). Take four rows from there:

let
    Source = Excel.CurrentWorkbook(){[Name="RawExport"]}[Content],
    Result = Table.Range(Source,3,4)
in
    Result

It skips the three junk lines and stops before the End of file row.

The result is just the readings:

Column1	Column2
temp-18C	OK
temp-19C	OK
temp-56C	HIGH
temp-20C	OK

You now have a clean block you can promote headers on, then trim with Table.SelectColumns and keep transforming.

Things to keep in mind with Table.Range

offset is zero-based, not one-based. To start at the second row you pass 1, not 2. Off-by-one results almost always trace back to this.
A count larger than what is left does not error. It just returns however many rows remain after the offset, so asking for ten rows when only three remain gives you three.
A negative offset throws. Expression.Error: The argument to function 'Table.Range' is out of range. Keep the offset at 0 or higher.
It works on row position, not row content. There is no condition or filter here. When you need rows that match a rule rather than a position, use Table.SelectRows instead.
Skip-from-the-top jobs have a simpler sibling. If you only want to drop the first N rows and keep everything else, Table.Skip reads more clearly than Table.Range with no count.

Common questions about Table.Range

What is the difference between Table.Range and Table.Skip?

Table.Skip only removes rows from the top and keeps the rest. Table.Range can do that too, but it also takes a count, so it can pull a fixed window out of the middle.

How is Table.Range different from Table.FirstN?

Table.FirstN always starts at the very first row. Table.Range lets you start at any offset, so it is the one to use when the block you want does not begin at the top.

List of All Power Query Functions

Related Power Query Functions / Articles: