Table.RemoveLastN

Updated on

Table.RemoveLastN is a Power Query M function that removes a specified number or condition-matching rows from the end 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.RemoveLastN(
   table as table,
   optional countOrCondition as any,
) as table

Description

The Table.RemoveLastN 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 bottom of your table.

The behavior of Table.RemoveLastN 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.

Examples

Let’s walk through how to use the Table.RemoveLastN function with a simple table, called Source:

Table.RemoveFirstN base table for Power Query M

Removing a Fixed Number of Rows

If you want to remove the last two rows from the table, use this code:

Table.RemoveLastN( 
  Source,
  2
 )

This removes the last two rows, leaving only the first two rows in the table.

Removing Rows Based on a Condition

You might also want to remove rows based on a condition. For example, to remove rows where CustomerID is greater than 1, use this expression:

Table.RemoveLastN( 
  Source,
  each [CustomerID] > 1
 )

This removes all rows where CustomerID is 2 or higher, leaving only the row with CustomerID = 1.

Removing the Last Row by Default

If you don’t specify the second argument, Table.RemoveLastN will remove just the last row by default:

Table.RemoveLastN( Source )

This will remove the last row, which has CustomerID = 4.

Full M code

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"]
  } 
),
    TableRemoveLastN_Count = Table.RemoveLastN( 
  Source,
  2
 ),
    TableRemoveLastN_Condition = Table.RemoveLastN( 
  Source,
  each [CustomerID] > 1
 ),
    RemoveLastRow = Table.RemoveLastN( Source )
in
    RemoveLastRow

Other functions related to Table.RemoveLastN are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy