List.ReplaceValue

Updated on
Rick de Groot

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 is tailored for partial matches within the 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.

Learn more about List.ReplaceValue in the following articles:

Other functions related to List.ReplaceValue are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy