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.
Related articles
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
Related functions
Other functions related to List.ReplaceMatchingItems are:
