List.ReplaceValue
ListReplaces occurrences of a specified value in a list with a new value, using a replacer function to perform the substitution.
Syntax
List.ReplaceValue(list as list, oldValue as any, newValue as any, replacer as function) as listParameters
| Name | Type | Required | Description |
|---|---|---|---|
list | list | Yes | The source list. |
oldValue | any | Yes | The value to search for and replace. |
newValue | any | Yes | The value to substitute in place of oldValue. |
replacer | function | Yes | A replacer function that determines the replacement logic. Use Replacer.ReplaceValue for exact replacement or Replacer.ReplaceText for text substitution. |
Return Value
list — A new list with the specified value replaced by the new value wherever it matches.
Remarks
List.ReplaceValue replaces items in a list using a replacer function that controls both the matching and substitution logic. The replacer parameter is required and must be one of two built-in replacer functions:
- Replacer.ReplaceValue — exact equality match: replaces items that equal oldValue with newValue. Works with any type (numbers, text, nulls, etc.).
- Replacer.ReplaceText — substring replacement: replaces occurrences of the text oldValue within each text item. Each item in the list must be a text value when using this mode.
All occurrences of oldValue in the list are replaced. Items that do not match are passed through unchanged. This function is the list counterpart of Table.ReplaceValue, which operates on table columns.
For replacing null values specifically, Replacer.ReplaceValue with null as oldValue is the clean approach. For more complex conditional replacements, List.Transform with an if expression gives more control.
Examples
Example 1: Replace a specific value using ReplaceValue
List.ReplaceValue({1, 2, 3, 2, 4}, 2, 99, Replacer.ReplaceValue)Result | |
|---|---|
| 1 | {1, 99, 3, 99, 4} |
Example 2: Replace null values with zero
List.ReplaceValue({10, null, 30, null}, null, 0, Replacer.ReplaceValue)Result | |
|---|---|
| 1 | {10, 0, 30, 0} |
Example 3: Replace text substring within each item using ReplaceText
List.ReplaceValue({"Hello World", "World Peace", "New World"}, "World", "Earth", Replacer.ReplaceText)Result | |
|---|---|
| 1 | {Hello Earth, Earth Peace, New Earth} |