Replacer.ReplaceValue
Replaces a whole value with a new value when it matches exactly, such as turning "N/A" into null.
Replacer.ReplaceValue(value, oldValue, newValue)Returns: The new value if the value matches, otherwise the original value.
What it does
Replacer.ReplaceValue changes a value only when the whole value matches the old value. It is the rule used by List.ReplaceValue and Table.ReplaceValue when you want exact swaps, such as converting the text "N/A", "-" or "TBD" into a real null or a zero. Partial matches are not changed.
Syntax and arguments
value: the current value. Power Query supplies this when you pass the function to a replace step.oldValue: the value to look for, matched in full.newValue: the value that replaces it. Usenullto blank it out.
Example
A payment file uses "-" for no amount and "N/A" for a line that has not been approved. Both should become null so that the remaining lines can be totalled cleanly.
let
Raw = {"1250", "-", "480", "N/A", "75"},
Blanked = List.ReplaceValue(Raw, "-", null, Replacer.ReplaceValue),
Blanked2 = List.ReplaceValue(Blanked, "N/A", null, Replacer.ReplaceValue),
Numbers = List.Transform(List.RemoveNulls(Blanked2), Number.FromText)
in
Numbers
Numbers is 1250, 480 and 75. The two placeholders are removed, and the rest are converted. A reviewer can see exactly which placeholder meant "blank".
Example: a second, different use
Swapping a legacy account code for its replacement in a mapping list, one value at a time.
Things to watch
- The match is for the whole value. "N/A " with a space at the end is not matched. Trim first.
- It is case sensitive by default. "n/a" and "N/A" are different values.
- Replacing with null hides the original meaning. Keep a note of what the placeholder meant, or keep a flag column.
- For text inside values, use Replacer.ReplaceText instead.
In business terms
Placeholder values such as "N/A", "-" and "TBD" in exports often mean different things. Converting them with a visible rule, and noting what each one meant, makes the cleanup easy to defend.