List.RemoveMatchingItems

Updated on
Rick de Groot

List.RemoveMatchingItems is a Power Query M function that removes all occurrences of the given values in a second list from the first list, with an optional equation criteria value to control equality testing. The function returns a modified list with the matching items removed.

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

Syntax

List.RemoveMatchingItems(
   list1 as list,
   list2 as list,
   optional equationCriteria as any,
) as list
ArgumentAttributeDescription
List1Specifies the list from which items are removed.
List2Specifies the list of values to remove.
Equation CriteriaOptionalSets the rules for comparing list values, such as case-insensitive matching with Comparer.OrdinalIgnoreCase or culture-specific comparisons using Comparer.FromCulture. If not specified, it defaults to Comparer.Ordinal.

Description

The List.RemoveMatchingItems function is designed to remove specific elements from a list based on a second list of values to be eliminated. The function allows for customized comparison criteria, such as case sensitivity or culture-specific rules, through an optional equationCriteria argument. If the items in the second list are not found in the first list, the original list remains unchanged.

Examples

Create a list from {1, 2, 3, 4, 5, 5} without {1, 5}.

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

Examples

The List.RemoveItems function is good at removing all instances of a particular value from a list. For example, if you have a list containing the numbers 1, 2, 3, 4, 5, and 5, and you want to remove the numbers 2 and 4, the function will eliminate all occurrences of these numbers.

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

Case-sensitive Removals

Capitalization can sometimes be crucial, especially when dealing with file names, email addresses, or programming variables. By default, List.RemoveMatchingItems performs a case-sensitive comparison, known as an ordinal comparison.

// capital sensitive search, output: { "a", "b", "c"}
List.RemoveMatchingItems( 
  { "a", "b", "c" },
  { "A", "B" } 
)

Which is equivalent to specifying the Comparer.Ordinal comparer function:

List.RemoveMatchingItems( 
  { "a", "b", "c" },
  { "A", "B" }, 
  Comparer.Ordinal
)

Case-Insensitive Removals

For scenarios where case sensitivity is important, you can use Comparer.OrdinalIgnoreCase. This is particularly useful when you’re dealing with data where the case of the letters can make a significant difference. For instance, consider a list of email addresses where you want to remove a specific email, paying attention to the case.

// Output: {}
List.RemoveMatchingItems( 
  {"john.doe@example.com", "John.Doe@example.com"}, {"John.Doe@example.com"},
  Comparer.OrdinalIgnoreCase 
)

Culture Sensitive Removals

In some situations, you may need to consider the cultural context when removing items from a list. You can use Comparer.FromCulture for this requirement. For example, the character “æ” in Danish is not equivalent to “ae” in English. When using the Comparer.FromCulture("en-US") function, the character “æ” is removed when you specify “ae” as the item to be removed.

// Output: { "a", "b", "c" }
List.RemoveMatchingItems(
  { "a", "b", "æ", "c" },
  { "ae" },
  Comparer.FromCulture("en-US")
)

However, if you switch to a Danish locale using Comparer.FromCulture("da-DK"), the function recognizes that “æ” and “ae” are not the same. As a result, the “æ” character remains in the list.

// Output: { "a", "b", "æ", "c" }
List.RemoveMatchingItems(
  { "a", "b", "æ", "c" },
  { "ae" },
  Comparer.FromCulture("da-DK")
)

This highlights the importance of specifying the correct cultural context when you need to perform culture-sensitive operations.

Learn more about List.RemoveMatchingItems in the following articles:

Other functions related to List.RemoveMatchingItems are:

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

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