Table.RemoveFirstN - Table Function

Table.RemoveFirstN is a Power Query M function that removes a specified number or condition-matching rows from the beginning of a table. The function returns a new table with the rows removed according to the countOrCondition parameter.

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

Syntax

Table.RemoveFirstN(
   table as table,
   optional countOrCondition as any,
) as table

Description

The Table.RemoveFirstN function returns a table that excludes a specified number of rows or rows that meet a condition. This is particularly useful when you need to remove unnecessary data from the top of your table.

The behavior of Table.RemoveFirstN depends on the value provided for the optional countOrCondition parameter:

When countOrCondition is omitted, the function removes only the first row by default.
When countOrCondition is a number, the function removes that many rows, starting from the top of the table.
When countOrCondition is a condition, the function removes rows that meet the condition until it encounters the first row that does not satisfy it.

Note that the Table.Skip function behaves identical to the Table.RemoveFirstN function, they’re syntax sugar for the same operation.

Examples

Let’s explore how to use the Table.RemoveFirstN function with a sample table, called Source:

Table.FromRecords( 
  {
    [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
    [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
  } 
)

This table appears as follows:

Table.RemoveFirstN base table for Power Query M

Removing a Fixed Number of Rows

To remove the first two rows from the table, you can use the following code:

Table.RemoveFirstN( 
  Source,
  2
 )

This will result in a table that only contains the remaining rows, starting from the third row.

Removing Rows Based on a Condition

In some cases, you might want to remove rows that meet a certain condition. For example, to remove all rows where CustomerID is less than 3, you can use this expression:

Table.RemoveFirstN( 
  Source,
  each [CustomerID] < 2
 )

This will skip the rows where the CustomerID is 1, and return the table starting from the rows where CustomerID is 2 or higher.

Skipping the First Row by Default

Since the second argument of Table.RemoveFirstN is optional, you can omit it to remove only the first row by default:

Table.RemoveFirstN( Source )

This removes just the first row (with CustomerID = 1) from the table.

For more detailed functionality, see the documentation for the Table.Skip function, which provides additional ways to handle row skipping based on numbers or conditions.

To try the full code, please paste the following script in the advanced editor:

let
  Source = 
    Table.FromRecords( 
  {
    [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
    [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
  } 
),
    TableRemoveFirstn_Count = Table.RemoveFirstN( 
  Source,
  2
 ),
    TableRemoveFirstN_Condition = Table.RemoveFirstN( 
  Source,
  each [CustomerID] < 2
 ),
    RemoveFirstRow = Table.RemoveFirstN( Source )
in
    RemoveFirstRow

Other functions related to Table.RemoveFirstN are:

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