Table.RemoveRows - Table Function

Table.RemoveRows is a Power Query M function that removes a specified number of rows from the beginning of a table, starting at a given offset. The function returns a new table with the rows removed, using a default count of 1 if not provided.

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

Syntax

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

Description

The Table.RemoveRows function removes rows from a table based on their position. You can specify an offset and optionally indicate how many rows to delete. When omitting the second argument, a single row will be deleted by default.

Examples

Let’s go through a couple of examples to show how Table.RemoveRows works.

Removing 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 remove the sixth row in the table, we can use the following code:

Table.RemoveRows( Source, 5 )

This tells Power Query to skip the first 5 rows and then remove the first row that follows. The result will be:

Table.RemoveRows removes rows without count from table in Power Query M

Removing a Specific Number of Rows from a Position

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

Table.RemoveRows( Source, 3, 2 )

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

Table.RemoveRows removes rows with count from table 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 }
      }
    ),
    TableRemoveRows_WithoutCount = Table.RemoveRows( Source, 5 ),
    TableRemoveRows_WithCount = Table.RemoveRows( Source, 3, 2 )
in
    TableRemoveRows_WithCount

Other functions related to Table.RemoveRows are:

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