List.ReplaceValue Function in Power Query M (6 Examples)

List.ReplaceValue searches a list for a value you specify and swaps in a replacement. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to clean up a list by replacing one value with another, whether that means swapping whole items or just part of the text inside them, this is the function you reach for.

Table of Contents

Syntax of List.ReplaceValue Function

List.ReplaceValue(list as list, oldValue as any, newValue as any, replacer as function) as list

where

list (required, list). The list you want to search through.
oldValue (required, any). The value to look for. It can be text, a number, or null.
newValue (required, any). The value to put in place of each match.
replacer (required, function). The function that decides how a match is made. This is almost always Replacer.ReplaceValue or Replacer.ReplaceText.

Returns: a new list the same length as the input, with every match replaced by newValue. If nothing matches, you get the original list back unchanged.

In plain terms, the replacer you pass is what decides the behavior. Replacer.ReplaceValue swaps whole matching items, while Replacer.ReplaceText swaps a substring inside each text item.

Example 1: Replace a whole value in a list

Replace every "red" in a list of colors with "orange", using Replacer.ReplaceValue.

let
    Source = List.ReplaceValue({"red","green","red","blue"},"red","orange",Replacer.ReplaceValue)
in
    Source

Result: {"orange", "green", "orange", "blue"}

Replacer.ReplaceValue matches on the whole item, so each element that exactly equals "red" becomes "orange". The others are left alone.

Example 2: Replace a substring inside each item

Here the values are short phrases, and you want to change just the word "red" to "blue" wherever it appears. That calls for Replacer.ReplaceText.

let
    Source = List.ReplaceValue({"red car","green car","red truck"},"red","blue",Replacer.ReplaceText)
in
    Source

Result: {"blue car", "green car", "blue truck"}

Replacer.ReplaceText looks inside each text item and swaps the substring, so "red car" becomes "blue car" even though the item never equals "red" on its own.

Example 3: See both replacers on the same list

This is the one to study, since it shows how much the replacer changes the outcome. Same list, same oldValue, very different results.

let
    WholeValue = List.ReplaceValue({"cat","category","scatter"},"cat","dog",Replacer.ReplaceValue),
    Substring = List.ReplaceValue({"cat","category","scatter"},"cat","dog",Replacer.ReplaceText),
    Combined = WholeValue & Substring
in
    Combined

Result: {"dog", "category", "scatter", "dog", "dogegory", "sdogter"}

The first three items are the Replacer.ReplaceValue run. Only "cat" matches exactly, so only it becomes "dog".

The last three are the Replacer.ReplaceText run. Every "cat" substring is swapped, so "category" turns into "dogegory" and "scatter" turns into "sdogter".

Example 4: Pull the value to replace from a table column

You often do not know the value to replace ahead of time. Here it comes from the first row of a Status column, and you want every matching status marked as "Complete".

Here is the starting data:

Status
Pending
Shipped
Pending

Read the column into a list, grab the first status, then replace it:

let
    Source = Excel.CurrentWorkbook(){[Name="Statuses"]}[Content],
    StatusList = Source[Status],
    OldStatus = Source{0}[Status],
    Replaced = List.ReplaceValue(StatusList,OldStatus,"Complete",Replacer.ReplaceValue)
in
    Replaced

Result: {"Complete", "Shipped", "Complete"}

OldStatus is "Pending" (the first row), so both "Pending" items become "Complete" and "Shipped" stays put. If you need to run the same replacement across a whole table instead of a single column, look at Table.ReplaceValue.

Example 5: What happens when the value is not found

If oldValue never appears in the list, there is nothing to replace. Here you look for "mango" in a list that does not contain it.

let
    Source = List.ReplaceValue({"apple","banana","cherry"},"mango","kiwi",Replacer.ReplaceValue)
in
    Source

Result: {"apple", "banana", "cherry"}

No match means no change. The function quietly returns the original list, it does not error. If you want to confirm a value is present before replacing, List.Contains gives you a true or false check.

Example 6: Replace null items with a placeholder

Because oldValue can be null, you can use List.ReplaceValue to fill in blanks. Here every null becomes "Unknown".

let
    Source = List.ReplaceValue({"North",null,"South",null},null,"Unknown",Replacer.ReplaceValue)
in
    Source

Result: {"North", "Unknown", "South", "Unknown"}

Replacer.ReplaceValue treats null like any other value to match, so each null is swapped for "Unknown".

Things to keep in mind with List.ReplaceValue

The replacer argument is required. Leaving it out throws Expression.Error: 3 arguments were passed to a function which expects 4. Pass Replacer.ReplaceValue or Replacer.ReplaceText.
Replacer.ReplaceText only works on text items. If your list holds numbers or other non-text values, use Replacer.ReplaceValue instead. Replacer.ReplaceText will throw when it hits a non-text item.
Replacer.ReplaceText can over-match. It replaces every occurrence of the substring, even inside longer words (Example 3 turned "category" into "dogegory"). When you mean whole items only, use Replacer.ReplaceValue.
Replacer.ReplaceValue matches the whole value, including numbers and null. That makes it safe for filling blanks (Example 6) and for swapping exact numeric values, not just text.
Matching is case-sensitive. "Red" will not match "red". Normalize the case first if you need a loose match.

Common questions about List.ReplaceValue

How is List.ReplaceValue different from List.Transform?

List.Transform applies a function to every item, so it is for transforming the whole list. List.ReplaceValue is a targeted find-and-replace that only touches items matching oldValue. Reach for List.Transform when every item needs the same change, and List.ReplaceValue when only specific values should be swapped.

List of All Power Query Functions

Related Power Query Functions / Articles: