Table.ReplaceValue
Replaces every occurrence of one value with another, in the columns you list.
Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as tableReturns: 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
- table: the table to change. Required.
- oldValue: the value to find, such as
"-"ornull. Required. - newValue: the value to put in its place, such as
nullor0. Required. - replacer: how to match.
Replacer.ReplaceValuematches the whole value.Replacer.ReplaceTextmatches text inside a value. Required. - columnsToSearch: a list of columns to search, in braces. Required.
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
- Replacer.ReplaceValue needs the whole value to match. Replacer.ReplaceText changes any match inside the text, which can change more than you meant.
- To replace blanks, use null as the old value.
- Name every column you want searched. Columns not in the list are left alone.
- Query folding: this step sometimes folds on database sources and sometimes does not. Check View Native Query.
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.