List.RemoveFirstN

Updated on
Rick de Groot

List.RemoveFirstN is a Power Query M function that removes the first element or the first 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 is empty.

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

Syntax

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

Description

The List.RemoveFirstN function in the Power Query M language is designed to remove a specified number of items from the beginning of a list. This removal can be based on a simple count or a more complex condition that the items must meet. The function returns a new list that excludes the removed items, making it a useful tool for data manipulation and cleaning.

When providing the countOrCondition, the function supports removing multiple values in the following ways:

  • When a numerical value is provided, that many items are removed from the beginning of the list.
  • If a condition is given, the resulting list starts with the first item that satisfies the condition. Once an item doesn’t meet the condition, the function stops considering further items.
  • If the optional argument is left null, only the first item in the list is removed.

Examples

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

Basic Usage: Removing a Fixed Number of Items

Imagine you have a list of integers, and you want to remove the first two items. The List.RemoveFirstN function makes this operation straightforward:

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

In this example, the function takes the list {1, 2, 3, 4} and removes the first two elements, returning a new list {3, 4}.

Advanced Usage: Adding a Condition

Sometimes, you may want to remove items based on a specific condition. For instance, consider a list of integers where you want to start with a number greater than 3:

// Output: { 4, 1 }
List.RemoveFirstN( { 3, 2, 4, 1 }, each _ <= 3 )

Here, the function removes all elements from the beginning of the list until it encounters a number greater than 3, returning a new list {4,1}. So even though the last item in the list is lower than 3, the removal of items stops once the function reaches the value 4.

In industrial settings, it’s common to have sensor readings where the initial values are zero due to calibration processes. You can easily remove these initial zeroes like so:

// Output: { 5, 6, 7 }
List.RemoveFirstN( { 0, 0, 0, 5, 6, 7 }, each _ = 0)

This example demonstrates how to clean up your data by removing the calibration zeroes, leaving you with the actual sensor readings {5, 6, 7}.

When dealing with sorted data sets, such as temperature readings, you may encounter outliers that are unusually low. These could be due to sensor errors or other anomalies. To clean up your data, you can use:

// Output: { 20, 21, 22 }
List.RemoveFirstN({-40, -35, 20, 21, 22}, each _ < 0 )

In this example, the function removes the first two unusually low temperature readings, -40 and -35, leaving you with a more reliable data set {20, 21, 22}.

By understanding how to use the List.RemoveFirstN function effectively, you can perform a wide range of data manipulation tasks. Whether you’re dealing with simple lists or complex data sets, this function offers the flexibility to trim down your lists.

Learn more about List.RemoveFirstN in the following articles:

Other functions related to List.RemoveFirstN are:

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

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