List.ReplaceValue
Replaces each item that matches a value with a new value, using a replacer function to decide what counts as a match.
List.ReplaceValue(list, oldValue, newValue, replacer)Returns: A list of the same length with the matching items replaced.
What it does
List.ReplaceValue finds items equal to a value you name and swaps them for another value. Typical uses are turning placeholder text such as "N/A" or "-" into a real null, or replacing an old account code with a new one. The fourth argument, the replacer, controls how the match is made.
Syntax and arguments
list: the list to change.oldValue: the value to look for.newValue: the value to put in its place. Usenullto blank it out.replacer: the matching rule. UseReplacer.ReplaceValueto match whole items. (For partial text inside an item, useReplacer.ReplaceTextwith a text list.)
Example
A list of amounts exported from a system that writes "N/A" when a value is missing and "-" for zero. The accountant wants blanks, so that later steps can remove them.
let
Raw = {"1250", "N/A", "480", "-", "75"},
Blanked = List.ReplaceValue(Raw, "N/A", null, Replacer.ReplaceValue),
Zeroed = List.ReplaceValue(Blanked, "-", "0", Replacer.ReplaceValue),
Numbers = List.Transform(List.RemoveNulls(Zeroed), Number.FromText)
in
Numbers
The result is the numbers 1250, 480, 0 and 75. The "N/A" becomes null and is then removed. The dash becomes zero, which is the meaning the export intended.
Example: a second, different use
Mapping an old chart-of-accounts code to a new one: replace "4010" with "4100" across a list of codes read from a mapping file.
Things to watch
- The match is on the whole item. "N/A " with a trailing space does not match "N/A". Trim the items first.
- The replacement must be the same type that the rest of the list expects. Mixing text and numbers gives type errors later.
- Replacing with null is common. Remove the nulls with List.RemoveNulls before you total the list, so the intent is clear to the next reader.
- In a table, use Table.ReplaceValue or Table.ReplaceErrorValues, which work on columns.
In business terms
Codes from old systems, placeholder text in exports and mapping of accounts during a migration are typical uses. A clear replacement step, with a note of the rule, is much easier to audit than a manual edit.