Downloads
Power Query MSplitters and combinersIntermediate

Replacer.ReplaceValue

Replaces a whole value with a new value when it matches exactly, such as turning "N/A" into null.

Syntax
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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics