Table.ReplaceValue

Table

Replaces occurrences of a value in specified columns using a replacer function.

Syntax

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

Parameters

Name	Type	Required	Description
`table`	`table`	Yes	The input table containing values to replace.
`oldValue`	`any`	Yes	The value to search for.
`newValue`	`any`	Yes	The replacement value.
`replacer`	`function`	Yes	The replacer function: Replacer.ReplaceValue for exact match, or Replacer.ReplaceText for substring replacement.
`columnsToSearch`	`list`	Yes	A list of column names in which to perform the replacement.

Return Value

table — The input table with the specified values replaced.

Remarks

Table.ReplaceValue searches for oldValue in the specified columns and replaces it with newValue. The replacer parameter determines the matching behavior:

- Replacer.ReplaceValue — performs an exact match on the entire cell value. Use this for replacing nulls, numbers, or exact text matches. - Replacer.ReplaceText — performs a substring replacement within text values. Use this for find-and-replace operations within strings.

If you have multiple replace operations on the same column, consider consolidating them into a single Table.TransformColumns call with a translation table or conditional logic. Multiple separate Table.ReplaceValue steps add unnecessary overhead.

Examples

Example 1: Replace exact values

Table.SelectColumns(
    Table.ReplaceValue(Sales, "East", "Eastern", Replacer.ReplaceValue, {"Region"}),
    {"CustomerName", "Product", "Region"}
)

Result

	CustomerName	Product	Region
1	Alice	Widget A	Eastern
2	Bob	Gadget B	West
3	Charlie	Widget C	Eastern
4	Alice	Gadget D	North
5	Diana	Widget A	West
6	Bob	Thingamajig E	Eastern
7	Charlie	Gadget B	West
8	Diana	Widget C	North

Example 2: Replace null values

let
    WithNull = Table.ReplaceValue(
        Table.SelectColumns(Table.FirstN(Sales, 4), {"CustomerName", "Region"}),
        "North", null, Replacer.ReplaceValue, {"Region"}
    )
in
    Table.ReplaceValue(WithNull, null, "Unknown", Replacer.ReplaceValue, {"Region"})