Downloads
Power Query MList: transform and filterIntermediate

List.ReplaceValue

Replaces each item that matches a value with a new value, using a replacer function to decide what counts as a match.

Syntax
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

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

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.

Related functions

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