List.RemoveRange - List Function

List.RemoveRange is a Power Query M function that removes a specified number of values in a list, starting at a specified position. The function returns a modified list with the specified range removed.

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

Syntax

List.RemoveRange(
   list as list,
   index as number,
   optional count as nullable number,
) as list

Argument	Attribute	Description
List		The list to remove values from.
Index		Specifies the starting position in the list where the removal of elements will begin. The index is zero-based, meaning the first element is at index 0, the second at index 1, and so on.
Count	Optional	Indicates the number of elements you want to remove, starting from the specified index. If this argument is omitted, the function defaults to removing just one element, located at the index specified.

Description

The List.RemoveRange allows you to remove a specific number of elements from a list, starting at a given index. This is particularly useful when you want to clean up your data or focus on a particular subset of your list. The function removes a single characters when the optional count argument is omitted.

Examples

Let’s look at a few examples of how to use the List.RemoveRange function.

Removing Negative Numbers from a List of Integers

Let’s say you’re working with a list of integers that contains both positive and negative numbers. For your specific analysis, you only want to focus on the positive numbers. Here’s how you can easily remove the negative numbers from your list:

// Output: { 1, 2, 3, 4, 5 }
List.RemoveRange( {1, 2, 3, 4, -6, -2, -1, 5}, 4, 3 )

In this example, the function removes three elements starting at index 4, effectively eliminating the negative numbers.

Removing a Single Item

Let’s say you have a list of temperature readings, and you notice that one reading at index 3 is unusually high due to a sensor error. You can remove this single outlier like so:

// Output: { 20, 21, 22, 24 }
List.RemoveRange( { 20, 21, 22, 110, 24 }, 3 )

By using only the first two arguments, the function removes just the single element at index 3, giving you a list that’s free of that particular anomaly.

Removing Multiple Outliers from a Data Set

Let’s say you collect another set of temperature readings the next day and notice that all the readings after the third one are unusually high. Again, this is likely due to a sensor error. Here’s how you can clean up your data:

// Output: {20, 21, 22}
List.RemoveRange( {20, 21, 22, 110, 120}, 3, 2 )

In this case, the function removes two elements starting at index 3, leaving you with a set of temperature readings that are more reliable.

Retrieving More Elements than Exist

Be cautious when specifying the number of elements you want to remove, especially if that number exceeds the available elements in the list. For example, the following expression tries to remove five items starting from index 3:

// Output: { error }
List.RemoveRange( {20, 21, 22, 110, 120}, 3, 5 )

Since there are only two elements available from index 3, Power Query will throw an error: “Expression.Error: There weren’t enough elements in the enumeration to complete the operation.”

By understanding the nuances of the List.RemoveRange function, you can easily remove specific items from your lists.

Other functions related to List.RemoveRange are:

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