List.ReplaceValue - List Function

List.ReplaceValue is a Power Query M function that searches a list of values for a specified value and replaces each occurrence with a replacement value. The function returns a modified list with the specified value replaced.

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

Syntax

List.ReplaceValue(
   list as list,
   oldValue as any,
   newValue as any,
   replacer as function,
) as list

Description

The List.ReplaceValue function modifies elements within a list by replacing (specific parts) of their values. This function operates by iterating through each element of a provided list and identifying segments within these elements that align with a defined criterion. When a match is discovered, the function replaces the identified segment with a newly specified value.

You can provide a replacer function to change the behaviour of the replacement. These functions dictate the replacement criteria and behaviour. The most commonly used replacer functions are Replacer.ReplaceText, which focuses on partial matches within elements, and Replacer.ReplaceValue, which targets exact matches of the entire element.

Examples

Let’s look at a few examples of how you can use the List.ReplaceValue function.

Replacing with Partial Match

Consider a list with values { "Bar", "CAT", "Tea" }. To replace each occurrence of the letter “a” with “1”, we use List.ReplaceValue in conjunction with Replacer.ReplaceText. This allows for partial matches within each string:

// Replaces with partial match { "B1r", "CAT", "Te1" }
List.ReplaceValue( 
  { "Bar", "CAT", "Tea" }, 
  "a", 
  "1",
  Replacer.ReplaceText 
)

In this example, Replacer.ReplaceText identifies any occurrence of “a” within the strings and replaces it with “1”. The function scans each element of the list and performs the replacement wherever a match is found.

Replacing with Exact Match

To replace values based on an exact match of the entire cell contents, use Replacer.ReplaceValue:

// Replaces with exact match { "Bar", "CAT", "Tea" }
List.ReplaceValue( 
  { "Bar", "CAT", "Tea" }, 
  "a", 
  "1", 
  Replacer.ReplaceValue 
)

In this scenario, since there is no element in the list that exactly matches “a”, the original list remains unchanged. Replacer.ReplaceValue looks for an exact match of the entire element against the specified value (“a” in this case) and replaces it only when a complete match is found.

If the input list changes and includes an element that exactly matches the specified value, the replacement will occur:

// Replaces with exact match { "1", "CAT", "Tea" }
List.ReplaceValue( 
  { "Bar", "CAT", "Tea" }, 
  "Bar", 
  "1", 
  Replacer.ReplaceValue
)

Here, because the list contains an element (“Bar”) that exactly matches the specified value, it is replaced with “1”. This illustrates how Replacer.ReplaceValue functions in the context of exact matches.

Defining a Custom Comparer Function

You may encounter scenarios where you need more control over how values are replaced within a list. To achieve this, you can define a custom comparer function. A custom comparer allows you to specify the logic for deciding when and how to replace values.

Suppose you want to replace specific values in a list using custom logic. This example replaces the value “3” with “2” but only when the current value matches the old value. The custom comparer function takes three parameters: currentValue, oldValue, and newValue. Here’s how it works:

// Returns: {null, "2", "2", "4" )
 List.ReplaceValue( 
  { null, "2", "3", "4"}, 
  "3", 
  "2",
  ( currentValue, oldValue, newValue ) => 
    if currentValue = oldValue then newValue else currentValue 
)

You can achieve the above with a regular replacer function too.

Now, let’s explore a scenario where the logic goes beyond simple equality. Imagine you want to replace all values whose numeric representation is greater than or equal to 2. For instance, if the text values "3" or "4" are converted to numbers and they meet this condition, they should be replaced with "9". Here’s how you can implement it:

// Returns: {null, "2", "9", "9" )
List.ReplaceValue( 
  { null, "2", "3", "4"}, 
  3, 
  "9",
  ( currentValue, oldValue, newValue ) => 
    if Number.From( currentValue ) >= oldValue then newValue else currentValue 
)

In this example:

Number.From(currentValue) converts the text values in the list to numbers for comparison.
Any value that meets the condition Number.From(currentValue) >= 2 is replaced with "9".

Learn more about List.ReplaceValue 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.ReplaceValue are:

Contribute » | Contributors: Rick de Groot, Aditya Kumar Darak
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-replacevalue