List.RemoveMatchingItems Function in Power Query

The List.RemoveMatchingItems function takes two lists and removes all occurrences of the second list values from the first list. If the values in list2 don't exist in list1, the original list is returned. An optional equation criteria value, equationCriteria, can be specified to control equality testing.

Syntax

List.RemoveMatchingItems(list1 as list, list2 as list, optional equationCriteria as any) as list

The function has the following parameters:
1. list1: The original list from which we want to remove items.
2. list2: The list of items we want to remove from list1.
3. equationCriteria (optional): Specifies how the items in list1 and list2 should be compared. This can be used to customize the comparison logic (e.g., case-insensitive comparison). We can use Comparer.OrdinalIgnoreCase for case-insensitive comparison.

Example: All occurrences of values "ashish", 40 and 60 are removed from the originalList.

Power Query M

let
    Source = List.RemoveMatchingItems({100, "Ashish", 26, 60, 60, 40, "Katrina"}, {"ashish", 40, 60})
in
    Source

The output of the above code is shown below:

List.RemoveMatchingItems function in Power Query

Example: Case-insensitive removal of items.

Power Query M

let
    Source = List.RemoveMatchingItems({100, "Ashish", 26, 60, 60, 40, "Katrina"}, {"ashish", 40, 60 }, Comparer.OrdinalIgnoreCase)
in
    Source

The output of the above code is shown below:

Previous Next