List.RemoveMatchingItems

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.RemoveMatchingItems

What is List.RemoveMatchingItems?

List.RemoveMatchingItems is a Power Query M function that allows you to remove all instances of a particular value from a list. This can be useful when you’re working with large data sets and need to quickly eliminate certain values. The syntax for List.RemoveMatchingItems is as follows:

List.RemoveMatchingItems(list as list, match as any, optional equationCriteria as function) as list

The first argument, list, is the list that you want to remove items from. The second argument, match, is the value that you want to remove. Finally, the optional third argument, equationCriteria, is a function that can be used to specify a custom comparison function.

How does List.RemoveMatchingItems work?

The List.RemoveMatchingItems function works by iterating over each item in the list and checking whether it matches the specified value. If a match is found, that item is removed from the list. This process continues until all instances of the specified value have been removed.

Examples of List.RemoveMatchingItems in action

Let's take a look at some examples of List.RemoveMatchingItems in action. In these examples, we'll assume that we have a list of values that contains a mix of numbers and text.

Example 1: Removing a single value from a list

Suppose we have the following list:

{1, “apple”, 3, “banana”, 5, “orange”, 7}

If we want to remove all instances of the value "apple" from this list, we can use the following code:

List.RemoveMatchingItems({1, “apple”, 3, “banana”, 5, “orange”, 7}, “apple”)

This will return the following list:

{1, 3, “banana”, 5, “orange”, 7}

Example 2: Removing multiple values from a list

Suppose we have the same list as in Example 1, but this time we want to remove both "apple" and "orange" from the list. We can do this using the following code:

List.RemoveMatchingItems({1, “apple”, 3, “banana”, 5, “orange”, 7}, {“apple”, “orange”})

This will return the following list:

{1, 3, “banana”, 7}

Example 3: Using a custom comparison function

Suppose we have a list of names that contains both uppercase and lowercase versions of the same name:

{“John”, “jane”, “Sarah”, “sarah”, “Bob”, “bob”}

If we want to remove all instances of the name "sarah" from this list, but want to ignore case when doing so, we can use a custom comparison function. Here's the code:

List.RemoveMatchingItems({“John”, “jane”, “Sarah”, “sarah”, “Bob”, “bob”}, “sarah”, (x,y) => Text.Lower(x) = Text.Lower(y))

This will return the following list:

{“John”, “jane”, “Bob”, “bob”}

In this article, we've explored the M code behind the Power Query M function List.RemoveMatchingItems. This function can be a powerful tool for quickly and easily removing specific values from lists. Whether you're working with large data sets or just need to clean up some messy data, List.RemoveMatchingItems can help you streamline your data cleaning process.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)