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
:
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
Related functions
Other functions related to Table.RemoveLastN are:
- Table.AlternateRows
- Table.Distinct
- Table.InsertRows
- Table.Range
- Table.RemoveFirstN
- Table.RemoveMatchingRows
- Table.RemoveRows
- Table.Repeat
- Table.Skip
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy