List.ReplaceMatchingItems Function in Power Query

The List.ReplaceMatchingItems function in Power Query (M language) is used to replace values in a list based on matching pairs from a replacement list. This is particularly useful when we want to substitute specific values in a list with new values.

Syntax

List.ReplaceMatchingItems(
    list as list, 
    replacements as list, 
    optional equationCriteria as any
) as list 

The function has the following parameters:

• list: The original list we want to modify.
• replacements: A list of lists, where each inner list is a pair [oldValue, newValue] that specifies what to replace and with what.
• equationCriteria: It is an optional parameter; it allows custom comparison.

How It Works:

For each element in the original list:
• If the element matches oldValue in any replacement pair, it's replaced with the corresponding newValue.
• If no match is found, the element is kept unchanged.

Example: Replace matching items in a list.

Power Query M

let
    originalList = {"Apple", "Banana", "Orange", "Apple"},
    replacements = {{"Apple", "Mango"}, {"Orange", "Grapes"}},
    updatedList = List.ReplaceMatchingItems(originalList, replacements)
in
    updatedList  

The output of the above code is:

{"Mango", "Banana", "Grapes", "Mango"}  

Notes: • The comparison is case-sensitive.
• Only the first match is replaced — if multiple replacements for the same value exist, only the first one is applied.
• Replacement is done based on exact match (not substring or pattern match).