List.RemoveItems - List Function

List.RemoveItems is a Power Query M function that removes all occurrences of the given values in one list from another list. The function returns the modified list or the original list if the values don’t exist in the first list.

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

Syntax

List.RemoveItems(
   list1 as list,
   list2 as list,
) as list

Description

The List.RemoveItems function is designed to eliminate specific values from a list. This function takes two lists as its arguments: the first list from which you want to remove items (list1), and the second list that contains the items you wish to remove (list2). If the items specified in list2 are not found in list1, the function simply returns list1 unchanged.

Examples

Let’s explore various examples to see how this function can be applied in different contexts.

Removing Numbers from a List

Let’s start with a simple example: you have a list of numbers – {1, 2, 2, 3, 4, 5, 5}. You decide you don’t need the numbers 2, 4, and 6 anymore. Here’s what you do:

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

In this example, the function scans through the list and removes all instances of the numbers 2 and 4. Since 6 is not present in the list, it does not affect the output. This operation simplifies the list by excluding specified elements, making it more relevant for subsequent processing.

Clearing Outliers from Sales Data

Now, let’s say you run a store, and you’re looking at your sales data: {500, 600, 700, 10000, 800}. That 10,000 looks weird—it’s much higher than the rest and might mess up your average. So, you decide to remove it:

// Output: {500, 600, 700, 800}
List.RemoveItems( {500, 600, 700, 10000, 800}, {10000} )

By removing the outlier, your sales data now looks more consistent. This helps you get a better sense of what a typical sales day looks like.

Updating a list of Usernames

Suppose you have a list of usernames: {“Alice”, “Bob”, “Cathy”, “Dave”}, and you’ve identified that “Dave” is an inactive user. To remove this username from your list, you can use:

List.RemoveItems( {"Alice", "Bob", "Cathy", "Dave"}, {"Dave"} )

This operation results in a list that only includes active users, which can be particularly useful for targeted communications or analytics.

Whether you’re working with simple lists or complex data sets, the List.RemoveItems function helps you remove specific data.

Learn more about List.RemoveItems in the following articles:

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
Removing Excess Spaces Between Words in Power Query
This article will showcase three effective methods to removing spaces between words gracefully. We’ll delve into strategies from creating recursive functions with the ‘@’ operator to employing ‘List.Generate’ for iterative replacements. We’ll also explore a unique approach to splitting and combining text values. » Read more

Other functions related to List.RemoveItems are:

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