List.RemoveLastN - Function

List.RemoveLastN is a Power Query M function that removes the last specified number of elements from a list. The function returns a list with the specified elements removed, or an empty list if the original list has fewer elements

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

Syntax

List.RemoveLastN(
   list as list,
   optional countOrCondition as any,
) as list

Description

The List.RemoveLastN function is designed to eliminate a specific number of items, or items that meet a certain condition, from the end of a given list. If the list has fewer items than the specified number to remove, the function returns an empty list.

When providing the optional countOrCondition parameter, you have the following options. You can provide a:

Numerical Value: If you specify a numerical value, the function will remove that many items from the end of the list.
Conditional Expression: If you provide a condition, the function will start from the end of the list and remove items until it encounters an item that fails to meet the specified condition.
Null Value: If this argument is left null or unspecified, the function will remove only the last item from the list.

Examples

Below, we’ll explore various examples to demonstrate how List.RemoveLastN can be effectively used in real-world scenarios.

Basic Usage: Removing a Fixed Number of Items

Let’s start with a simple scenario. Imagine you have a list of numbers, and you need to eliminate the last two elements from it. The List.RemoveLastN function is perfect for this:

// Output: { 1, 2 }
List.RemoveLastN( { 1, 2, 3, 4 }, 2 )

In this straightforward example, the function takes the original list of {1, 2, 3, 4} and trims off the last two elements. The resulting list is {1, 2}, just as you’d expect.

Advanced Usage: Adding a Condition

Sometimes, you need a more nuanced approach to list manipulation. For example, consider a list of software versions where the last few entries are beta releases that you’d like to exclude:

// Output: { "v1.0", "v1.1", "v1.2" }
List.RemoveLastN( 
  { "v1.0", "v1.1", "v1.2", "v2.0-beta", "v2.1-beta" }, 
  each Text.EndsWith(_, "-beta") 
)

In this example, the function scans the list from the end and removes all versions that conclude with “-beta,” leaving you with a list of stable releases: { “v1.0”, “v1.1”, “v1.2” }.

Suppose you have a list of temperature readings, and you notice that the last few are abnormally high. You can easily remove these outliers:

// Output: { 70, 75, 80 }
List.RemoveLastN( { 70, 75, 80, 110, 120 }, each _ > 100 )

Here, the function removes the last two temperature readings—110 and 120—since they exceed 100, leaving you with a more accurate set of data: { 70, 75, 80 }.

In a list of registered users, you might find that the last few are marked as inactive. To clean up your list, you can use the following code:

// Output: { "User1", "User2", "User3" }
List.RemoveLastN( 
  { "User1", "User2", "User3", "Inactive", "Inactive" }, 
  each _ = "Inactive" 
)

In this scenario, the function removes the last two entries marked as “Inactive,” resulting in a cleaned-up list of active users: { “User1”, “User2”, “User3” }.

By mastering the List.RemoveLastN function, you can tackle a wide array of data manipulation challenges. Whether you’re working with simple numerical lists or more complex data sets, this function provides flexibility in removing items from the end of your list.

Learn more about List.RemoveLastN in the following articles:

Get Value from Previous Row using Power Query
This article shows how to get the previous row value using Power Query. You can adjust the 3 ways to also retrieve an earlier or later row. » Read more
Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Learn more about List.RemoveLastN in the following articles:

Get Value from Previous Row using Power Query
This article shows how to get the previous row value using Power Query. You can adjust the 3 ways to also retrieve an earlier or later row. » Read more
Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.RemoveLastN are:

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