Table.ReplaceValue Function in Power Query M (5 Examples)

If you want to find and replace values in a Power Query table, Table.ReplaceValue is the function that does it. You point it at the columns to search, give it the old and new values, and tell it whether to match the whole cell or just part of the text.

Table of Contents

Syntax of Table.ReplaceValue Function

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

where

table (required, table). The table you want to run the replacement on.
oldValue (required, any). The value to look for in the listed columns.
newValue (required, any). The value to put in place of each match.
replacer (required, function). Use Replacer.ReplaceValue to match a whole cell value, or Replacer.ReplaceText to swap a substring inside text.
columnsToSearch (required, list). The list of column names to search, like {"Priority"}.

Returns: a new table with the matching values replaced in the listed columns. The original table is unchanged.

In plain terms, you tell it what to find, what to replace it with, and which columns to look in.

Example 1: Replace a whole value in one column

You have a Tickets table and want to swap every P1 priority for Critical.

Here is the starting data:

TicketID	Priority
T-501	P1
T-502	P10
T-503	P1

Run Table.ReplaceValue with Replacer.ReplaceValue on the Priority column:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets"]}[Content],
    Replaced = Table.ReplaceValue(Source,"P1","Critical",Replacer.ReplaceValue,{"Priority"})
in
    Replaced

This replaces every cell that equals P1 exactly.

The result is:

TicketID	Priority
T-501	Critical
T-502	P10
T-503	Critical

Notice P10 is left alone. Replacer.ReplaceValue matches the whole value, and P10 is not equal to P1.

Example 2: Fix a misspelling inside text

Here you want to correct recieved to received wherever it shows up in a Note column.

Here is the starting data:

LogID	Note
L-1	Parcel recieved at depot
L-2	Customer recieved refund
L-3	Order shipped

Use Replacer.ReplaceText so it works on the substring, not the whole cell:

let
    Source = Excel.CurrentWorkbook(){[Name="Notes"]}[Content],
    Replaced = Table.ReplaceValue(Source,"recieved","received",Replacer.ReplaceText,{"Note"})
in
    Replaced

This swaps the misspelled word inside each note while keeping the rest of the text.

The result is:

LogID	Note
L-1	Parcel received at depot
L-2	Customer received refund
L-3	Order shipped

The Order shipped row stays as is because it never contained recieved.

Example 3: Replace across multiple columns

You can search more than one column in a single call. Here N/A should become Unknown in both City and Region.

Here is the starting data:

Store	City	Region
S-1	N/A	West
S-2	Boise	N/A
S-3	Reno	South

List both columns in columnsToSearch:

let
    Source = Excel.CurrentWorkbook(){[Name="Sites"]}[Content],
    Replaced = Table.ReplaceValue(Source,"N/A","Unknown",Replacer.ReplaceValue,{"City","Region"})
in
    Replaced

This applies the same replacement to every column in the list.

The result is:

Store	City	Region
S-1	Unknown	West
S-2	Boise	Unknown
S-3	Reno	South

The Store column is untouched because it was not in the list.

Example 4: Replace null with a default number

To swap blank cells for 0, the column needs to be typed as a number first.

Here is the starting data:

SKU	Units
A-100	12
B-200
C-300

Set the type with Table.TransformColumnTypes, then replace null with 0:

let
    Source = Excel.CurrentWorkbook(){[Name="Inventory"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units",Int64.Type}}),
    Replaced = Table.ReplaceValue(Typed,null,0,Replacer.ReplaceValue,{"Units"})
in
    Replaced

The Replacer.ReplaceValue matches each null and writes 0 in its place.

The result is:

SKU	Units
A-100	12
B-200	0
C-300	0

Both empty cells now hold 0, and the existing 12 is left alone.

Example 5: Watch the difference between whole value and substring

This shows why the replacer choice matters. You want VIP to become Gold in a Tier column.

Here is the starting data:

MemberID	Tier
M-1	VIP
M-2	VIP Member
M-3	Standard

Using Replacer.ReplaceText matches VIP anywhere in the cell:

let
    Source = Excel.CurrentWorkbook(){[Name="Members"]}[Content],
    Replaced = Table.ReplaceValue(Source,"VIP","Gold",Replacer.ReplaceText,{"Tier"})
in
    Replaced

Because it works on the substring, it also changes the start of VIP Member.

The result is:

MemberID	Tier
M-1	Gold
M-2	Gold Member
M-3	Standard

If you only wanted the exact VIP cell changed, you would use Replacer.ReplaceValue instead, and VIP Member would stay as is.

Things to keep in mind with Table.ReplaceValue

The type of oldValue must match the column. Replacing null or a number on a text-typed column can throw an Expression.Error, which is why Example 4 sets Int64.Type before replacing. Type the column first.
columnsToSearch is always a list, even for one column. Write {"Priority"}, not "Priority".
Text matching is case-sensitive. Replacer.ReplaceText treats VIP and vip as different values, so cased variants are not replaced unless you target them.
You can pass your own replacer function. The two built-ins cover most cases, but the replacer argument accepts any function with the right shape if you need custom logic.
It changes values, not column names. To rename a column instead of its contents, reach for 0.

Performance and query folding

Table.ReplaceValue usually does not fold to the source, so on a database it tends to run locally after the data is pulled in. On Excel, CSV, or folder sources it always runs locally, which is fine for the row counts you normally work with in Power Query.

Common questions about Table.ReplaceValue

How do I replace values based on a condition?

Use Replacer.ReplaceText only when you can express it as a fixed substring swap. For anything conditional, wrap the logic in a custom function passed as the replacer argument, or use Table.ReplaceValue together with a transform like Table.TransformColumns that reads each value and decides what to write.

List of All Power Query Functions

Related Power Query Functions / Articles: