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

When you need to drop specific rows from a table by their position, Table.RemoveRows does the job. You give it a starting point and how many rows to remove, and it returns the table without those rows. This is the function to reach for when you want to delete rows by where they sit, not by what they contain.

Table of Contents

Syntax of Table.RemoveRows Function

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

where:

table (required, table): The table you want to remove rows from.
offset (required, number): The zero-based position where removal starts. The first row is offset 0, the second row is offset 1, and so on.
count (optional, nullable number): How many rows to remove starting at offset. When you leave it out, count defaults to 1.

Returns: a table with the specified rows removed

It hands back a new table that matches the original, minus the count rows that started at offset.

Example 1: Remove the First Row

Say you have a support-ticket table and the top row is a header artifact or a stale entry you want gone. You can drop it with offset 0 and let count default to 1.

Here is the starting data:

TicketID	Agent	Priority
TKT-401	Maya	High
TKT-402	Raj	Low
TKT-403	Lena	Medium
TKT-404	Owen	High
TKT-405	Priya	Low

Remove the first row by pointing at offset 0:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets1"]}[Content],
    Result = Table.RemoveRows(Source,0)
in
    Result

The result keeps:

TicketID	Agent	Priority
TKT-402	Raj	Low
TKT-403	Lena	Medium
TKT-404	Owen	High
TKT-405	Priya	Low

The TKT-401 row is gone. Because count was left off, only that one row got removed.

Example 2: Remove the First Two Rows

This time you want both of the top two rows gone. Start at offset 0 again, but set count to 2.

Using the same ticket table, remove the first two rows:

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

The result keeps:

TicketID	Agent	Priority
TKT-403	Lena	Medium
TKT-404	Owen	High
TKT-405	Priya	Low

Both TKT-401 and TKT-402 are removed. The count of 2 controls how many rows go, counting from the offset.

Example 3: Remove Rows From the Middle

Removal does not have to start at the top. Set offset to 2 to begin at the third row, then remove 2 rows from there.

Using the same ticket table, remove two rows from the middle:

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

The result keeps:

TicketID	Agent	Priority
TKT-401	Maya	High
TKT-402	Raj	Low
TKT-405	Priya	Low

The rows at offset 2 and 3 (TKT-403 and TKT-404) are gone. Everything before and after them stays put.

Example 4: Remove the Last Row Without Hard-Coding Its Position

Hard-coding the last row’s offset breaks the moment your row count changes. Instead, compute the position with Table.RowCount, a close cousin of List.Count. Since offset is zero-based, the last row sits at Table.RowCount(Source)-1.

Using the same ticket table, remove the last row dynamically:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets4"]}[Content],
    Result = Table.RemoveRows(Source,Table.RowCount(Source)-1)
in
    Result

The result keeps:

TicketID	Agent	Priority
TKT-401	Maya	High
TKT-402	Raj	Low
TKT-403	Lena	Medium
TKT-404	Owen	High

The bottom row TKT-405 drops off, and the formula keeps working no matter how many rows the table grows to.

Things to Keep in Mind With Table.RemoveRows

A count that runs past the end does not silently clamp. It throws Expression.Error: There weren't enough elements in the enumeration to complete the operation. To stay safe, clamp count yourself, for example List.Min({count,Table.RowCount(Source)-offset}), so you never ask for more rows than exist beyond offset.
offset is zero-based. The first row is offset 0, the second is offset 1, and so on. It is easy to be off by one if you think in normal row numbers.
count defaults to 1 when you leave it out, so Table.RemoveRows(Source,3) removes a single row at offset 3. This row-based function has a column-based counterpart in Table.SelectColumns and its opposite, which work on columns instead.
Removal is order-dependent. Table.RemoveRows works on the rows as they currently sit, so use Table.Sort first if you need a predictable result.
Table.RemoveRows removes rows by their position, while Table.SelectRows keeps or drops rows by a condition you write. Use position-based removal when the location matters and condition-based filtering when the content matters.

Common Questions About Table.RemoveRows

What is the difference between Table.RemoveRows and Table.SelectRows?

Table.RemoveRows deletes rows based on where they are in the table, using an offset and a count. Table.SelectRows keeps rows that pass a condition, like each [Priority]="High". Pick Table.RemoveRows when position is what you care about, and condition-based filtering when the row’s values decide its fate.

How do I remove the last N rows?

The cleanest option is Table.RemoveLastN, which removes a set number of rows from the bottom without any position math. You can also reverse the table with Table.ReverseRows, then use Table.RemoveFirstN, though Table.RemoveLastN is simpler for this.

List of All Power Query Functions

Related Power Query Functions / Articles: