List.ReplaceMatchingItems - List Function

List.ReplaceMatchingItems is a Power Query M function that performs specified replacements in a list, with an optional equation criteria value to control equality testing. The function returns a modified list with the specified replacements made.

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

Syntax

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

Argument	Attribute	Description
list		List of values to replace items in.
replacements		Replacements provided as a list of lists.
equationCriteria	optional	Uses Comparer Functions to determine how values are equated during operations. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

Description

The List.ReplaceMatchingItems function replaces specific items in a list based on defined matching criteria. It takes two primary inputs: a list of items to be processed, and a list of replacement pairs, where each pair specifies an item to be replaced and its corresponding replacement value. The function iterates through the input list, replacing items that match any of the specified pairs in the replacement list.

By default the function replaces values in a case-sensitive way (identical to Comparer.Ordinal). You can optionally provide an equation criteria parameter to define how matches are determined, such as Comparer.OrdinalIgnoreCase for case-insensitive matching. The function returns a new list with the specified replacements made, while preserving the original list’s order and structure.

Examples

Consider a list of numbers: { 1, 2, 3, 4 }. To replace a single item, for instance, replacing 1 with 10, we use the following expression:

// Output: { 10, 2, 3, 4 }
List.ReplaceMatchingItems( { 1, 2, 3, 4 }, { { 1, 10 } } )

This example demonstrates the basic functionality of List.ReplaceMatchingItems, where a single element is replaced by another.

Multiple Item Replacements

The function is not limited to replacing just one item. You can simultaneously replace multiple items:

List.ReplaceMatchingItems( { 1, 2, 3, 4 }, { { 1, 10 }, { 3, 30 } } )
// Output: { 10, 2, 30, 4 }

Here, multiple pairs of replacements are performed in a single operation, showing how the function handles multiple transformations.

Text Value Replacement

The function also works with text values:

// Output: { "z", "b", "c" }
List.ReplaceMatchingItems( { "a", "b", "c" }, { { "a", "z" } } )

In this instance, a text value is replaced, instead of a numeric values.

Case Sensitivity in Replacements

List.ReplaceMatchingItems is case-sensitive by default:

// Output: { "a", "b", "c" }
List.ReplaceMatchingItems( { "a", "b", "c" }, { { "A", "Z" } } )

This example returns the original list, as there is no exact case match.

Case-Insensitive Replacement

For case-insensitive replacements, the function can be modified using Comparer.OrdinalIgnoreCase:

// Output: { "z", "b", "c" }
List.ReplaceMatchingItems( { "a", "b", "c"}, { { "A", "z" } }, Comparer.OrdinalIgnoreCase )

This variation allows for flexible replacements regardless of case differences.

Learn more about List.ReplaceMatchingItems in the following articles:

Replace Values in Power Query M (Ultimate Guide)
Learn how to replace values in Power Query. Look into conditional replacements, replacing in multiple columns, case sensitivity and more! » Read more
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

Other functions related to List.ReplaceMatchingItems are:

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