Table.ReplaceValue - Table Function

Table.ReplaceValue is a Power Query M function that replaces an oldValue with a newValue in the specified columns of a table. The function returns a table with the replaced values.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
Table		The table you want to modify.
oldValue		The value you want to search for and replace. You can specify a constant, a column reference, or conditional logic.
newValue		The value you want to use as a replacement. You can specify a constant, a column reference, or conditional logic.
Replacer		The method of replacement, which can be a comparer function like Replacer.ReplaceValue or Replacer.ReplaceText, or a custom comparer function.
columns to search		A list of column names within which to perform the search and replace operation.

Description

The Table.ReplaceValue function allows you to replace specified values in a table. You can target specific columns and define what values you want to replace and what to replace them with. The function also supports conditional logic and custom replacement methods for more advanced scenarios.

Examples

Let’s say you have a table named ‘Source,’ and all the text values in this table contain the text “Green.”

Basic Replacement with Built-in Replacer Functions

Assume you have a table named ‘Source’ that contains the text “Green” in multiple cells. The Table.ReplaceValue function supports two primary built-in replacers: Replacer.ReplaceText and Replacer.ReplaceValue.

Using Replacer.ReplaceText:

Table.ReplaceValue(
  Source, 
  "Green", 
  "Yellow",
  Replacer.ReplaceText, 
  {"ReplaceText"} 
)

And using Replacer.ReplaceValue:

Table.ReplaceValue(
  Source, 
  "Green", 
  "Yellow",
  Replacer.ReplaceValue, 
  {"ReplaceValue"} 
)

Applying both functions on the dataset results in the following table.

Table.ReplaceValue differences between Replacer.ReplaceText and Replacer.ReplaceValue

These two replacers function differently. When Replacer.ReplaceText is used, every instance of “Green” changes to “Yellow,” but note that this is case-sensitive. If “green” appears in lowercase, no change will be made.

On the other hand, Replacer.ReplaceValue only swaps the text when the entire cell content matches “Green.” Hence, only cells containing exactly “Green” will get updated.

If you’d like to try this out for yourself, you can paste the following code into the Power Query M Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTc1T0oHSsTpQkfLM5AyYKJgNl1EIzsgsKoHJQXkg2XSoSRA6NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ReplaceText = _t, ReplaceValue = _t]),
    ReplaceText = Table.ReplaceValue(Source,"Green","Yellow",Replacer.ReplaceText,{"ReplaceText"}),
    ReplaceValue = Table.ReplaceValue(ReplaceText,"Green","Yellow",Replacer.ReplaceValue,{"ReplaceValue"})
in
    ReplaceValue

Conditionally Replace Values

The replacer functions get even more useful when you start applying conditional logic to replace values. To make this clear, let’s delve into an example with a specific dataset.

Table.ReplaceValue Conditional Replace dataset

To follow along, copy the below code into a query in a step called Source.

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY7NCsIwEIRfRXLuS6hFelCQFryUHhazNEtjEjZdsH16N7GnGebbnxlH0wHzZhrzpH0H1Q5S2szUKEH+UAyo4TklX/QW35LRVtzHoMlFeEZW06OH74HuspTpB/klO6j+KkxR8h8jlU8DeLCqLa7lUziWW+DVnV5g69lBsqPSIMxac5p+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Food = _t, Mood = _t])

Replacing Every Value in a Column

If you want to replace every single value in the ‘Food’ column, you can execute the following command:

Table.ReplaceValue(
  Source, 
  each [Food], // Replace all values in the 'Food' column
  each 1, // Replace values with number value 1
  Replacer.ReplaceValue, 
  {"Food"} 
)

Running this code changes all values in the ‘Food’ column to the value 1.

Table.ReplaceValue-Replace-All-values-in-a-column-by-another

Notice that the above example uses Replacer.ReplaceValue. If you switch to Replacer.ReplaceText, the column values remain unchanged. The reason is that the new value 1 is not a text value.

Yet when you change the new value to “1”, the replacement works just fine.

= Table.ReplaceValue(
  Source, 
  each [Food], // Replace all values in the 'Food' column
  each "1", // Replace values with text value "1"
  Replacer.ReplaceText, 
  {"Food"} 
)

Conditional Replacements: Adding Precision

But what if you want to be more specific with your replacements? You can introduce conditions into the NewValue argument, like so:

Table.ReplaceValue(
  Source, 
  each [Food], // Replace all Values in the 'Food' column
  // If the condition is true replace with 'macaroni', otherwise return the original value
  each if Text.StartsWith( [Name], "H") then "Macaroni" else [Food], 
  Replacer.ReplaceValue, 
  {"Food"}
)

Here, the code alters the values in the ‘Food’ column only when the corresponding ‘Name’ begins with an “H.”

Table.ReplaceValue conditionally replace value

Using Conditions in Both ‘OldValue’ and ‘NewValue’ Arguments

You can take it a step further by placing conditions in both the ‘OldValue’ and ‘NewValue’ arguments. Check out the following example:

Table.ReplaceValue(
  Source, 
  each if [Mood] = "Happy" then [Food] else null, 
  each if Text.StartsWith( [Name], "H") then "Macaroni" else [Food], 
  Replacer.ReplaceValue, 
  {"Food"}
)

Table.ReplaceValue conditionally replace old and new value

In this more complex example, the ‘Food’ column values are replaced only for rows where the ‘Mood’ is “Happy.” For other rows, the ‘OldValue’ is set to null, which means only null values are replaced. The main difference between this example and the earlier one is that the conditions have become more nuanced. Now, not every row will meet the criteria for a text replacement.

There’s a remaining issue though. Have you noticed how the data type in the above example is lost? The reason for this is that the custom replacer function we provided, does not provide a ‘return type’ assertion. To keep the right data type you can adjust the code to:

Table.ReplaceValue(
  Source, 
  each [Food], 
  (_) as text => if Text.StartsWith( [Name], "H") then "Macaroni" else [Food], 
  Replacer.ReplaceValue, 
  {"Food"}
)

Creating Custom Replacer Functions

The Table.ReplaceValue function also accepts custom replacer functions. This enables you to apply complex replacement logic based on your specific requirements.

Syntax Overview

Let’s first try to understand the syntax of a custom replacer function. It can be simulated like this:

(currentValue, oldValue, newValue) => 
  if oldValue then newValue else currentValue

This custom function examines the current value in a cell, compares it to the old value you want to replace, and then swaps it with the new value if there’s a match.

Table.ReplaceValue dataset for custom comparer function

Application of a Custom Replacer

Let’s say you’ve got a table with columns named ‘Food’ and ‘Mood.’ You can employ a custom replacer function like this:

Table.ReplaceValue(
  Source, 
  each true, // Replace all values
  each "All", // With "All"
  (currentValue, oldValue, newValue) =>  if oldValue then newValue else currentValue, 
  {"Food", "Mood"} 
)

Table.ReplaceValue Custom Replacer Function to replace values in multiple columns without data type

This custom function checks whether the oldValue condition is met, which in our case is always true. Then, it proceeds to replace every value in both the ‘Food’ and ‘Mood’ columns with the newValue, which is “All”.

However, just like with the previous example, we run into the issue that data types are not defined. To define a data type in the same expression, you can include a ‘return type’ for the custom function of argument 4. That looks as follows:

Table.ReplaceValue(
  Source, 
  each true, // Replace all values
  each "All", // With "All"
  (currentValue, oldValue, newValue) as text =>  if oldValue then newValue else currentValue, 
  {"Food", "Mood"} 
)

The following table that includes data types is the result:

Table.ReplaceValue Custom Replacer Function to replace values in multiple columns including data type

Limitations of Built-in Replacers

Now, you might wonder, “Couldn’t I have just used a built-in comparer function for this?” The answer is a resounding no. Here’s why:

If you tried the same replacement using Replacer.ReplaceValue, it would look like this:

Table.ReplaceValue(
  Source, 
  each [Food], // For each row take the value in the 'Food' column as OldValue
  each "All", // Replace the oldValue by 1
  Replacer.ReplaceValue, 
  {"Food", "Mood"} // Perform replacement in these columns
)

Table.ReplaceValue problem with conditional replacing multiple columns

While this function does replace values in multiple columns, the values it targets for replacement (oldValues) are exclusively based on the ‘Food’ column. In instances where ‘Food’ and ‘Mood’ don’t have the same values, no replacement would occur.

This article shows the flexibility and use of creating your own custom replacer functions. It allows you to go beyond the limitations of built-in comparer functions, providing a custom approach to modifying your data the way you want.

Learn more about Table.ReplaceValue in the following articles:

Replace Values in Power Query M (Ultimate Guide)
Learn how to replace values in Power Query. Look into conditional replacements, replacing in multiple columns, case sensitivity and more! » Read more

Other functions related to Table.ReplaceValue are:

Table.ReplaceMatchingRows

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-replacevalue