Replace Values in Power Query – Ultimate Guide

Replacing values is a common transformation for cleaning data. Power Query allows you to easily replace values using the user interface. Yet, this is often only for the more simple find and replace operations. Once you want to add conditions, sometimes even on multiple columns, things get more complicated. On top of that, you may run into case-sensitivity issues.

This and more I will discuss in this article. After reading through, you understand everything you need to know about the area of replacing values.

The data we will work with in this post is the following customer data:

1. Fundamentals

Replacing values is a useful skill to pick up. Before heading to the more advanced topics, we start with the fundamentals.

1.1. How to Replace Values?

In its most basic form, replacing values in Power Query is easy. When you want to replace values in a column, you can either:

1. Right-click on a column -> Select Replace Values

2. Go to the Transform tab -> click on Replace Values

3. You can right-click a value within a column and click on Replace Values

After any of the 3 steps, the Replace Values pop-up screen appears.

1.2. Arguments Table.ReplaceValue

Now let’s have a look at how replacing a value works. In the Marital_Status column you want to replace the text value Together by the words Living Together. To do that, in the replace values screen fill in:

  • Value to find: Together
  • Replace with: Living Together
Replace Values pop-up screen

After pressing OK, Power Query performs the replacement. If this was the first time you replaced a value, congrats! As you can see it is very easy.

Let’s have a closer look at the code the replacing operation generates. The function Table.ReplaceValue does the replacing and requires 5 arguments. Below you find the code for the replacing we just did:

After formatting the code for clarity, you can find the function arguments below:

= Table.ReplaceValue(
     #"Changed Type",
     "Together",
     "Living Together",
     Replacer.ReplaceText,
     {"Marital_Status"}
  )

The 5 arguments within the function are:

ArgumentDescription
1. Table as tableThis is the table where values are replaced
2. Old Value as anyThe value that you want to replace by a new value
3. New Value as anyThe value you want to replace the old value by
4. Replacer as functionOne of replace functions:
– Replacer.ReplaceValues
– Replacer.ReplaceText
5. Columns to search as listColumn to replace values in

1.3. ReplaceValue vs ReplaceText

There is a difference between replacing text values and replacing other data types. In the previous example, we replaced a text value by using:

= Table.ReplaceValue(
     #"Changed Type",
     "Together",
     "Living Together",
     Replacer.ReplaceText,
     {"Marital_Status"}
  )

This code is automatically generated when you replace values on a text column. You can also replace number values like year of birth. By changing year from 1957 to 1958, the code looks like:

= Table.ReplaceValue(
     #"Replaced Value",
     1957,
     1958,
     Replacer.ReplaceValue,
     {"Year_Birth"}
  )

The difference between the two examples is in the 4th argument, the replacer function. The argument can call one of two functions:

  1. Replacer.ReplaceText
  2. Replace.ReplaceValue

You can use both in the Table.ReplaceValue and the List.ReplaceValue functions to substitute values. And even though they both replace items, their behavior is different. The Replacer.ReplaceText version can also replace substrings. Yet Replacer.ReplaceValue only replaces entire cell contents.

What does that mean? Imagine you want to replace the text Graduation by Undergraduate. When the entire cell-contents match, the replacing succeeds as in below picture.

Yet, when you try to replace a substring of the string, the replacement fails.

Changing the replacer function to the text version allows you to replace the partial text.

The replace values pop-up screen for text values looks like below. Can you predict what happens when you check the Match Entire Cell Contents box under the Advanced options? Power Query then uses the Replacer.ReplaceValues function. Whereas the unchecked version uses the ReplaceText version.

So the Replacer.ReplaceValue function replaces text when the entire cell contents match. The other way around does not work. Applying the Replacer.ReplaceText function on a number column, results in the error: “Expression.Error: We cannot convert the value 1965 to type Text“.

2. Replace in Multiple Columns

So far you replaced values in a single column. But what if you want to replace in more than one column? No problem. To do that, you can select multiple columns by holding the Ctrl-key and clicking on each column you need. With your columns selected, right click on the column name -> press Replace Values.

If your selected columns are all text, the code generated for the replacements looks like:

= Table.ReplaceValue(
     #"Replaced Value2",
     "a",
     "b",
     Replacer.ReplaceText,
     {"ID", "Year_Birth", "Education"}
)

Notice how line 6 now shows all the column names you selected for the replace operation, within a list. So the key to applying a simple replace operation on several columns is to include the columns in a list in the 5th argument of Table.ReplaceValue.

Yet there is something to watch out for. If one of your column types is different than text, your code will include the replacer function Replacer.ReplaceValue.

= Table.ReplaceValue(
     #"Changed Type",
     "a",
     "b",
     Replacer.ReplaceValue,
     {"ID", "Year_Birth", "Education"}
)

When that happens, know that this only replaces text values with an exact match. In other words, matching the entire cell contents. If this is undesired behavior you can either:

  1. change the data types of all the selected columns to text
  2. perform two separate replace operations, one for text and one for other data types.

3. Conditional Replace: Single Column

Often it is not enough to replace column values. You may want to replace values in a column based on a condition, and only if the condition is true you substitute values. The long way to do that is to:

  1. add a custom/conditional column
  2. build your if-condition and return the correct values
  3. remove the old column
  4. rename your newly added column to the desired name

These are a lot of steps to conditionally replace a value. So how can you replace values with an if condition in an easier way? The dataset has a column containing the Income of our customers. You want to maximize the incomes in the dataset and only want salaries up to €50.000. Higher income amounts should be shown as €50.000. To do this, you first prepare your code.

Instead of writing this from scratch you can generate a template by:

  • Right click the income column -> click Replace Values
  • Fill in a random number for Value to Find and value to Replace With
  • Press OK.

This generates the following setup:

= Table.ReplaceValue(
     #"Changed Type",
     5,
     6,
     Replacer.ReplaceValue,
     {"Income"}
 )

You can use this as a template to start from. The above code shows the Value To Find (= 5) in argument 2 and Replace With in argument 3 (= 6). Both values are hardcoded. When you conditionally replace values you want to rewrite these two arguments.

Instead of hardcoding a value to find, you can write:

= Table.ReplaceValue(
     #"Changed Type", 
     each [Income], 
     each if [Income] > 50000 then 50000 else [Income] ,
     Replacer.ReplaceValue,{"Income"}
 )

Each [Income] tells the Table.ReplaceValue function to replace each value from the column Income. It is good to realize this is the same column name you see in argument 5.

The next argument contains the if condition to check for before replacing values: each if [Income] > 50000 then 50000 else [Income]. The each word indicates the function applies the logic for all lines individually.

In other cases, you may want to conditionally replace one column by the values of another column. It may be obvious to some, but it is good to realize you can do this with the same code. The following example returns a Customer’s Year of Birth when their income is lower than €50.000. The example shows that you can change one column based on another column.

4. Conditional Replace: Multiple Columns

There may be times where you want to apply the same transformation on multiple columns. As we saw earlier, doing a regular replace operation on multiple columns is simple. You add the columns to the list of columns to search for. But what if you want to conditionally replace values of multiple columns in a single step, is that possible?

4.1. Limited Version

To some extent this is possible with earlier formula, but it has a big limitation. Let’s see an example. Have a close look at below dataset. You will try to conditionally replace values for the columns KidHome and Teenhome. Notice that out of 11 rows, 4 row values are identical.

When we replace values on multiple columns, the first question is: which values do we replace? Let’s say we replace the values from the column Kidhome by the value 2. And instead of inputting only KidHome as a column to search in, you also input Teenhome on line 5.

= Table.ReplaceValue(
     #"Removed Other Columns", 
     each [Kidhome], 
     each 2, 
     Replacer.ReplaceValue,{"Kidhome", "Teenhome"}
)

This replaces the marked values by the number 2. When going through the column values, each line is checked for equality with the values of Kidhome. This by definition replaces all values from the column Kidhome, and replaces the Teenhome column values only for those that matched with the same line in the Kidhome-column.

To get accurate results, the function would have to know the appropriate column context. So it would know that it should replace values in the current column. Power Query unfortunately does not know a Column Context concept. That is why this method proves difficult in applying conditional logic for replacements on several columns. So what is a better way to approach this?

4.2. In a Single Step

Since the previous method did not succeed, we will change to an entirely different approach. This approach replaces the Value to Look for and the replacer function.

= Table.ReplaceValue(
     #"Removed Other Columns", 
     each [Income] > 50000, 
     each 2, 
     (x,y,z)=> if y then z else x,
     {"Kidhome", "Teenhome"} 
)

Ziying35 showed this approach on a forum. I do not fully grasp the inner workings of (x,y,z)=> if y then z else x yet. What surprises me is that it does not use any of the replacer functions from the documentation. If someone can shine a light on its workings, please let me know in the comments. Else, I didn’t want to keep the method from you. The formula conditionally replaces values on multiple columns in a single step.

4.3. Using Unpivot

A third approach involves reshaping your data. When working with conditional replacements on multiple columns, the unpivot operation is your friend. You can unpivot the columns that require replacements. But first, notice how there is no unique ID in the dataset of the above picture. If we unpivot without having a column that is unique, we will bump into trouble Pivoting the data again later. To solve this:

  • go to Add Column -> click on Index Column
  • now select the columns Kidhome and Teenhome, right click the column name -> select Unpivot Columns

From here you can replace the items in the Value column using the method from previous chapter. The following code would replace each item belonging to customers with an income of less than €50.000 by 2 and otherwise returns the original value.

= Table.ReplaceValue(
     #"Unpivoted Other Columns", 
     each [Value], 
     each if [Income] >= 50000 then 2 else [Value],
     Replacer.ReplaceValue,{"Value"}
 )

The only thing left to do is to then Pivot the data back to its original form by:

  • Select the Attribute column -> Go to Transform, click on Pivot Column
  • Make sure the Values Column is set to Value
  • And set the Aggegrate Value Function to Don’t Aggregate

If your sort order is important, you can now sort your data by the index column, and conclude the exercise by removing the index column. You have now conditionally replaced values on multiple columns, albeit with a workaround.

So are the extra steps worth it? When applying your logic to a great number of columns this will make your life easier. Also, adding an Index column is not always required. When your data contains a unique identifier you can skip this step. In the end you decide if the steps are worth the hassle. But at least you now know another way to do it.

5. Bulk Replace Using Translation Table

The replacements so far have been with a single value to find and a single value to replace it with. Sooner or later, you will want to replace multiple items in bulk, each into a different result. Imagine replacing 20 values, this would involve a lot of steps in your query. Instead of adding separate steps for each replacement, you will now learn how you can batch replace values in a single step.

To get started, you need a table that contains the replacement values. We will use the following table that has the name TranslationTable. It contains values you want to replace (Old) and the values you want returned (New). This operation only replaces values if the column contains the keyword in the column Old.

Imagine you want to replace the values in below data, with the new values in the translation table.

For this operation we will use the Text.Replace function. This function has three arguments that all need to be text values:

  • The text to replace
  • Old value
  • New Value

A simple replace of the letters ‘rilla’ you can do by adding the following in a custom column:

= Text.Replace( [Consumer_ID],  "rilla", "RICKK" )

The text replacement is case-sensitive and replaces substrings. It’s good to know that the Text.Replace function only does a single replacement at a time. For batch replacements you need to call the function multiple times using another function. Candidates for these are List.Accumulate and List.Generate. In this example you will use the List.Accumulate version as shared by Gil Raviv.

The List.Accumulate function receives the [Consumer_ID] text and replaces the [Old] Text with the corresponding [New] text. The function that does the replacement is the following:

=List.Accumulate(
   Table.ToRecords( ReplacementTable ), 
     [Consumer_ID], 
      ( valueToReplace, replaceOldNewRecord ) => 
         Text.Replace( valueToReplace, 
                       replaceOldNewRecord[Old], 
                       replaceOldNewRecord[New]
         )
  )

It is out of the scope of this article to go deeper into the List.Accumulate function. You can find separate articles for that. Simply notice how:

  • Line 2 references the query name of the translation table, which is ReplacementTable.
  • Line 3 contains the column name with the text values to replace, which is [Consumer_ID].
  • Line 6 and 7 reference the columns in the replacement table, namely [Old] and [New].
  • The values valueToReplace and replaceOldNewRecord are no actual table or column names, but are temporary variables within the function created in line 4.

For now, you can add this to a custom column to see how it replaces the relevant values.

You can improve the query performance by wrapping the ReplacementTable into the List.Buffer function. This prevents Power Query from accessing external queries multiple times, improving the load times.

=List.Accumulate(
   List.Buffer( Table.ToRecords( ReplacementTable ) ), 
     [Consumer_ID], 
      ( valueToReplace, replaceOldNewRecord ) => 
         Text.Replace( valueToReplace, 
                       replaceOldNewRecord[Old], 
                       replaceOldNewRecord[New]
         )
  )

Last but not least, you can bulk replace multiple values in a single step by replacing values through:

= Table.ReplaceValue(
     #"Changed Type", 
     each [Consumer_ID],  
     each List.Accumulate(
        List.Buffer( Table.ToRecords( ReplacementTable ) ), 
          [Consumer_ID], 
           ( valueToReplace, replaceOldNewRecord ) => 
               Text.Replace( valueToReplace,                
                             replaceOldNewRecord[Old],           
                             replaceOldNewRecord[New] ) ),
     Replacer.ReplaceText,
     {"Consumer_ID"}
 )

For now, don’t worry too much about the intricacies of the function used here. This deserves more attention in a separate article.

6. Replace Values: Case-Insensitive

By default Power Query replaces values with a case-sensitive operation. In this chapter you learn how to ignore case while replacing values. Replacing the text rIck to Rick in below dataset, replaces only the second value. And the user interface does not give you any options to replace case-insensitive. So what methods can you use?

6.1. Containing Keyword

To check if your string contains the Value to Find, and then replace the entire cell contents, you can cook up a conditional replace formula in Power Query with:

= Table.ReplaceValue(
     #"Changed Type",
     each [Consumer_ID], 
     each if Text.Contains([Consumer_ID] ), "rick", 
           Comparer.OrdinalIgnoreCase ) then "Rick" else 
           [Consumer_ID],
     Replacer.ReplaceValue,
     {"Consumer_ID"}
 )

The formula checks whether the column contains the text rick and does that case-insensitive. The Comparer.OrdinalIgnoreCase part instructs the Text.Contains function to ignore the case. Meaning that differences in capital letters do not influence your result. When there is a match, the value changes to Rick, otherwise the original value remains.

So what if you want to replace text case-insensitive, not only for rick but also for multiple keywords?

6.2. Bulk Replace Using Manual List

You may want do a big cleanup of a dataset when it’s full of spelling errors. And even for multiple replacements Power Query can help out. To replace multiple items case-insensitive, you can use the List.ReplaceMatchingItems function.

The function has two mandatory arguments and an optional one:

  1. List as list (to perform replacements on)
  2. Replacements as list ( the old value and the new value to replace it by, provided as list.
  3. optional equation criteria (that determines the case-sensitivity)

To start, you want to replace Rick by RICk. Let’s first try this in a custom column by adding:

= List.ReplaceMatchingItems( 
     { [Consumer_ID] }, 
     { { "Rick", "RICk" } } ) 
 )

In human language this instructs to replace the values in the column [Consumer_ID], equal to Rick (case-sensitive) by RICk.

The result of the replace operation is a list. To inspect one of these list items, you can click within a cell that contains the list (don’t click on the word ‘list’ itself). A preview then appears in the bottom. That preview shows us that your replace operation succeeded.

To make it easier to check the other values, you can wrap the formula in Text.Combine. This function take a list as its argument and concatenates all values in the list. Since we have a single value, it will simply return that value.

= Text.Combine(
       List.ReplaceMatchingItems( 
          { [Consumer_ID] }, 
          { { "Rick", "RICk" } }
          )
   )

As above picture shows, the code only replaces the value in row 1. This happens because by default the List.ReplaceMatchingItems function replaces values in a case-sensitive way.

To fix this, you can add the third optional argument which is the equation criteria or comparer function. The comparer function Comparer.OrdinalIgnoreCase instructs functions to ignore the capitals when replacing values. So your adjusted formula looks like this:

= Text.Combine(
       List.ReplaceMatchingItems( 
          { [Consumer_ID] }, 
          { { "Rick", "RICk" } },
          Comparer.OrdinalIgnoreCase
          )
   )

Resulting in below transformed dataset:

This replaces the cells where the entire cell contents equal the word Rick, while ignoring case. You now only have 1 more requirement. You want to be able to perform multiple replacements in a single formula. That last step you can do by expanding the lists with values to replace.

List.ReplaceMatchingItems accepts “a list of two values, the old value and new value, provided in a list“, as the documentation shows. In the example that means that we can add more items within the outer list of the second argument:

= Text.Combine(
       List.ReplaceMatchingItems( 
          { [Consumer_ID] }, 
          { { "Rick",        "RICk"        }, 
            { "GorillaBI",   "GORILLABi"   }, 
            { "RickdeGroot", "RICKDEGROOt" } },
          Comparer.OrdinalIgnoreCase
          )
   )

And with that you have now replaced multiple items, case-insensitive with a match on the entire cell contents.

6.3. Bulk Replace Using Translation Table

You may have noticed this is still a manual list to maintain. Which is not very convenient for large amount of replacements, nor for maintenance. So how can we improve this? One way is to use a replacement table to save the values to replace in. Let’s say you have a table named ReplacementTable with the columns Old and New.

Instead of the manual list of replacements, you can automate this. And for that you got to know a little more about a function called List.Zip. The function takes a list of lists as its argument and then transforms it into another list. A simple example of this would be below:

= List.Zip( { {"a", "b", "c", "d"}, {1, 2, 3, 4} } )

The result of the function is:

{ { "a", 1 }, 
  { "b", 2 }, 
  { "c", 3 },
  { "d", 4 } }

It takes the first item of the first list, and matches it with the first item of the second list. And then it does that for the second value of each list and the third, creating a list of pairs. And those pairs are the instructions we need to replace the values. Our original code looked like:

= Text.Combine(
       List.ReplaceMatchingItems( 
          { [Consumer_ID] }, 
          { { "Rick",        "RICk"        }, 
            { "GorillaBI",   "GORILLABi"   }, 
            { "RickdeGroot", "RICKDEGROOt" } },
          Comparer.OrdinalIgnoreCase
          )
   )

Lines 4-6 represent a similar list of pairs. With that in mind, you can leverage List.Zip to create the list with replacements. To do that you reference the ReplacementTable and add the column name behind it. A column reference always results in a list with the values of that column. And this list we can provide to List.Zip.

= List.Zip(  { ReplacementTable[Old], 
               ReplacementTable[New]  } 
 )

This creates the final formula:

= Text.Combine(
       List.ReplaceMatchingItems( 
          { [Consumer_ID] }, 
          List.Zip( { ReplacementTable[Old], 
                      ReplacementTable[New] } ),
          Comparer.OrdinalIgnoreCase
          )
   )

For performance reasons you can then wrap the List.Zip into the List.Buffer function:

= Text.Combine(
       List.ReplaceMatchingItems( 
          { [Consumer_ID] }, 
          List.Buffer( 
             List.Zip( { ReplacementTable[Old], 
                         ReplacementTable[New] } ),
          Comparer.OrdinalIgnoreCase
          )
   )

Hopefully this post inspired you to experiment with replacing values. It is very likely you have found other ways to replace values. If you have an interesting one that you want to share, feel free to drop it in the comments. And who knows I may add it to the list.

To inspect the examples used in this post you can download the example file:

Other than that, enjoy Power Query!

About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

4 thoughts on “Replace Values in Power Query – Ultimate Guide”

Leave a comment