Replacer.ReplaceValue
Replaces a value when it equals the old one as a whole: the replacer for whole cells.
Group: Splitters, combiners, replacers and comparers. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Replacer.ReplaceValue(value as any, old as any, new as any) as any| Parameter | Type | Description |
|---|---|---|
value | any | The value to test. |
old | any | The value to look for. |
new | any | What to give instead. |
Returns
any. The new value when the value equals the old one; the value itself otherwise.
Remarks
- It works on any kind of value: numbers, dates, null. Text must match whole: replacing "Oslo" leaves "Oslo City" alone.
Examples
Zeros to null
let
Stock = #table({"Product", "Stock"}, {{"Chair", 0}, {"Desk", 10}, {"Lamp", 0}})
in
Table.ReplaceValue(Stock, 0, null, Replacer.ReplaceValue, {"Stock"})| Product | Stock |
|---|---|
| Chair | null |
| Desk | 10 |
| Lamp | null |
Whole values only
let
Shops = #table({"Shop"}, {{"Oslo"}, {"Oslo City"}})
in
Table.ReplaceValue(Shops, "Oslo", "OSL", Replacer.ReplaceValue, {"Shop"})| Shop |
|---|
| OSL |
| Oslo City |
Related functions
Replacer.ReplaceText, Table.ReplaceValue, List.ReplaceValue.