Downloads
Power Query MTable: values and fillBeginner

Table.ReplaceValue

Replaces every occurrence of one value with another, in the columns you list.

Syntax
Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

Returns: The table with the matching values replaced.

What it does

Table.ReplaceValue finds a value and swaps it for another value, across the columns you name. Bank files often show zero as a dash, and a report may need a label changed from "Acct." to "Account". This step handles both. The replacer decides whether the whole value must match or only part of it.

Syntax and arguments

Example

A bank export shows a dash in the Debit and Credit columns where there is no amount. The dash is replaced with a blank, so the columns can be converted to numbers.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    NoDashes = Table.ReplaceValue(Source, "-", null, Replacer.ReplaceValue, {"Debit", "Credit"}),
    Typed = Table.TransformColumnTypes(NoDashes, {{"Debit", type number}, {"Credit", type number}})
in
    Typed

Only the whole value "-" is replaced. A dash inside a longer description is not touched, because the replacer matches whole values.

Example: a second, different use

Descriptions use the abbreviation "Acct." inside longer text. Replacer.ReplaceText changes the abbreviation wherever it appears.

let
    Source = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    Expanded = Table.ReplaceValue(Source, "Acct.", "Account", Replacer.ReplaceText, {"Description"})
in
    Expanded

Things to watch

In business terms

Cleaning placeholder codes such as dashes, "N/A" and "n/a" before totals is one of the most common preparation steps for an export. Doing it in the query makes the rule the same every month.

Related functions

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