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 listDescription
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 say you have a list of integers: {1, 2, 2, 3, 4, 5, 5}. You want to remove all occurrences of the numbers 2, 4, and 6. Here’s how you can achieve that:
// 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 first list and removes all instances of the numbers 2 and 4. The number 6 is not in the list, so it remains unchanged.
Imagine you’re analyzing a list of monthly sales figures: {500, 600, 700, 10000, 800}. You notice that the value 10,000 is an outlier that could distort your analysis. To remove it, you can use:
// Output: {500, 600, 700, 10000, 800}
List.RemoveItems( {500, 600, 700, 10000, 800}, {10000} )This leaves you with a list that better represents your typical monthly sales, making your data more reliable for further analysis.
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.
Related articles
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 tackle this issue 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
Related functions
Other functions related to List.RemoveItems are:
- List.Difference
- List.Distinct
- List.Intersect
- List.RemoveFirstN
- List.RemoveLastN
- List.RemoveMatchingItems
- List.RemoveNulls
- List.RemoveRange
- List.Skip
