Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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.

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

Table of contents

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

1. The dataset for replacing values

Fundamentals

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

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. Simple Replace values

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

3. Simple Replace values Transform tab

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.

Table.ReplaceValue Arguments

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 with the words Living Together. To do that, in the replace values screen fill in the following:

  • 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 will find the code for the replacement we just did:

4. Replace values function

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 replacer functions:
Replacer.ReplaceValue
Replacer.ReplaceText
5. Columns to search as listColumn to replace values in

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 the year from 1957 to 1958, the code looks like this:

5. Function for Replacing Number Values
= 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. Replacer.ReplaceValue

Important

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

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

6. Replacer.ReplaceValue entire cell contents succeeds

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

6. Replacer.ReplaceValue entire cell contents fails

Changing the replacer function to Replacer.ReplaceText allows you to replace the partial text.

7. Replacer.ReplaceText replace substring

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.ReplaceValue function. Whereas the unchecked version uses the ReplaceText version.

7. Replacer.ReplaceText Advanced Options

Caution

The Replacer.ReplaceValue function replaces text when the entire cell contents match. Unfortunately, the reverse 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“.

Replace in Multiple Columns

So far you have replaced values in a single column. But what if you want to replace 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 a column name and press Replace Values.

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

= 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.

Tip

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 be careful. If one of your column types is different from type text, your code will include the replacer function Replacer.ReplaceValue.

That could look as follows:

= 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 behavior is undesired , 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.

Conditional Replace: Single Column

Replace Values Based on Condition (in a Single Step) in Power Query

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:

7.9. Conditionally Replace Values Single Column
= 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.

7.99. Conditionally Replace Values Single Column From Other column

You can achieve the same result with the following even simpler code suggested by StunDexim:

= Table.TransformColumns( 
     #"Remove nulls", 
     {"Income", each if _ > 50000 then 50000 else _ }
)

Conditional Replace: Multiple Columns

Replace Values in Multiple Columns Based on Condition in Power Query

There may be times when 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?

Limited Version

To some extent, this is possible with earlier formulas, but it has a big limitation. Let’s see an example. Have a close look at the 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.

8. Conditionally Replace Values Multiple Columns Data set

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 with 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"}
)
9. Conditionally Replace Values Multiple Columns Search list 1

This replaces the marked values with 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?

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"} 
)

In this example, we define our own custom function. The custom function we define in the third argument, requires three input values in the order of (currentValue, oldValue, newValue). In below situation we conditionally replaces value on multiple columns:

10. Conditionally Replace Multiple Columns Single step

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
10. Conditionally Replace Multiple Columns Unpivot data 1

From here you can replace the items in the Value column using the method from the previous chapter. The following code would replace each item belonging to customers with an income of less than € 50.000 by 2 and otherwise return 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 Pivot the data back to its original form by:

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

Tip

If your sort order is important, you can now sort your data by the index column. You can then 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.

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 with 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.

14. Case insensitive replacement table

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

11. Case sensitive dataset

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" )
4. Bulk Replace using

The text replacement is case-sensitive and replaces substrings.

Tip

Be aware 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 the following 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. In the above code, what’s important to notice is that:

  • 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].
  • Lines 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 in below image:

4. Bulk Replace List.Accumulate formula

Tip

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.

To improve our query performance, we’ll incorporate the List.Buffer function as shown in below code:

= 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.

Read more: How to use Lists in Power Query – Complete Guide »

Replace Values: Case-Insensitive

Perform a Case Insensitive Replace Values in Power Query

By default Power Query replaces values with a case-sensitive operation. In this section, you learn how to ignore case while replacing values. Replacing the text rIck to Rick in the 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?

11. Case sensitive dataset

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"}
 )

This formula checks whether the column contains the text rick and whether it is 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?

Bulk Replace Using Manual List

You may want to 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:

= List.ReplaceMatchingItems(
     list         as list,             // list to perform replacements on
     replacements as list,             // list of replacements
     optional equationCriteria as any) // determines case-sensitivity
as list

Let’s first have a look at replacing a single-word text value. To start, you want to replace Rick with 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.

12. Case insensitive replace multiple items

The result of the replace operation is a list. A preview then appears at the bottom. That preview shows us that your replace operation succeeded.

Tip

To inspect one of these list items, you can click the whitespace within a cell that contains the list (don’t click on the word ‘list’ itself).

To make it easier to check the other values, you can wrap the formula in Text.Combine. This function takes 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" } }
       )
   )
13. Case insensitive replace multiple items concatenated

As the 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. You will find this comparer function appears in many other list functions in Power Query.

Your adjusted formula looks like this:

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

Resulting in the transformed dataset as shown below:

13. Case insensitive replace multiple items preparation

This replaces the cells where the entire cell contents equal the word Rick, while ignoring the 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.

Note

List.ReplaceMatchingItems accepts “a list of two values, the old value and new value, provided in a list“.

To perform multiple replacements, we can adjust the code to 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
       )
   )
13. Case insensitive replace multiple items formula

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

Bulk Replace Using Translation Table

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

14. Case insensitive replacement table

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 this:

= 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 a 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]  }  )

// With a 2 column replacement table you can achieve the same with: 
= Table.ToColumns( ReplacementTable ) 

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
      )
 )

Bulk Replace Sentences

So far the case-insensitive replacements took a place on a single-word text value. When your text consists of a sentence with multiple words, you will have to adjust the approach slightly. Since you want to replace a value in a case-insensitive manner, it’s important that the replacement takes place on the entire word, regardless of which letter is capitalized. You can achieve that by first splitting the sentence into words, then performing a case-insensitive replacement on the entire value, and concatenating them back together.

= Text.Combine( 
     List.ReplaceMatchingItems ( 
          Text.Split( [Text], " " ) , 
          List.Buffer( List.Zip( Table.ToColumns( ReplacementTable ) ) ), 
          Comparer.OrdinalIgnoreCase 
      ), 
  " ")

Alternatively, you could use the following solution provided by Bill Szysz, which is a little slower.

= let ReplaceTbl = Table.Buffer( ReplacementTable ) in 
Text.Combine( 
     List.Transform( 
          Text.Split( [Text], " " ), 
          each try ReplaceTbl[New]{ List.PositionOf( ReplaceTbl[Old],  _) }
                        otherwise _ ), 
" " )
 

Replace Substrings in Bulk

Last but not least, if you’re looking for a way to replace multiple text values in Power Query easily, good news! List.Accumulate is here to save the day. And if you’re new to this function, this guide gets you started.

List.Accumulate lets you perform an action like replacing substrings in bulk on a dataset repeatedly.

Picture this: you have a dataset with a column called “Value” and a bunch of replacements to make. Here’s a neat way to do it:

List.Accumulate (
  { { "A", "Ab" }, { "B", "Bc" }, { "C", "Cd" }, { "D", "De" } }, 
  Source, 
  ( state, current ) =>
    Table.ReplaceValue ( 
      state, 
      current{0}, 
      current{1}, 
      Replacer.ReplaceText, { "Value" } )
)[MyTable]

So, what’s happening here? List.Accumulate iterates through the value combinations you need to replace. It starts by replacing “A” with “Ab,” then “B” with “Bc,” and so on. Just make sure to set up the initial seed values, or Power Query might miss them.

Similarly, you can use a translation table by changing the code to:

List.Accumulate (
  List.Zip( { replacementTable[Old], replacementTable[New]  } ), 
  Source, 
  ( state, current ) =>
    Table.ReplaceValue ( 
      state, 
      current{0}, 
      current{1}, 
      Replacer.ReplaceText, { "Value" } )
)[MyTable]

And that’s it! Now you can replace substrings in Power Query in bulk without breaking a sweat. Good luck!

Conclusion

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:

Thanks for visiting and enjoy Power Query!

Share on:
  1. for blank value replacement i can use like following code.

    Table.ReplaceValue(#”Added Custom2″,””,”F&O”,Replacer.ReplaceValue,{“F&O”})

    how to use above similar code for nonblank values.

    Reply
  2. Power Query Find Text and Delete Anything After
    Excel Power Query what would be the M Code for finding the String “NUL” and then remove any Text after the “NUL” String. I have several serial numbers for example;
    1287JLPO-NUL-00-000-00000
    HLQA-00-NUL-000-0000
    JRP-01-NUL-00-WB00-000
    I just need to delete any text after NUL. As you can see, I can’t just replace – or 0 because those could be found prior to NUL.

    Reply
  3. Hello Rick; Can you tell me how to perform the following in Power BI. I’m bit of a novice and could really use your help. I have two columns, the first column contains in each cell a string of numbers about 7 digits long. The first two digits of each number is either a 45, 60, 85 or 90. In the second column, if the first two digits are 45 or 60, I would like the cell in the second column to return “DIRECT”. Otherwise, INDIRECT will appear in second column. If blank, return in the second column “Non_PO”. Can you assist me step by step in how to do this?

    Reply
  4. Dear Rick,

    I´m German and i need your support. I’m trying to implement Bulk Replace Using Translation Table to replace a subset of characters. I have a query named ‘Data_Table’. There are two columns named ‘ColorID’ and ‘Color’. The Replacement table named ‘Find_ReplaceTable’ with the columns ‘Find’ and ‘Replace’. The column ‘Find’ contains ColorID´s and the column ‘Replace’ contains color names. I try to assign the value of color names based on the auxiliary FindReplaceTable. If the value in ‘ColorID’ matches then replace the color names from the auxiliary FindReplaceTable. The following code doesn’t work entirely correctly.
    Please, do you have any idea how to proceed? You are my last hope.
    Thanks in advance.

    Nico

    Table.ReplaceValue(#"Geänderter Typ",
       each [Color], 
       each List.Accumulate(List.Buffer(Table.ToRecords(Find_ReplaceTable)), [ColorID],
               (valueToReplace, replaceOldNewRecord) => Text.Replace( valueToReplace,               
                         replaceOldNewRecord[Find],          
                         replaceOldNewRecord[Replace])),Replacer.ReplaceText,{"Color"}
    )
    Reply
  5. This is another ways of transformation for mutliple column

    Table.FromRecords (
      Table.TransformRows (
        #"Changed Type", 
        ( x ) =>
          Record.TransformFields (
            x, 
            List.Transform ( { "Col1", "Col1" }, ( y ) => { y, each if _ = null then 115 else null } )
          )
      )
    )
     Table.FromRecords (
      Table.TransformRows (
        #"Changed Type", 
        ( x ) =>
          Record.TransformFields (
            x, 
            {
              { "Col1",  each if _ = null and _ > 0 then 115 else if _ = null and _  0 then 105 else if _ = null and _ < 0 then - 105 else null
              }
            }
          )
      )
    )
    Reply
  6. Hello Rick,
     
    I try to assign the value of merchant category based on the auxiliary FindReplace table while using partial match (case-insensitive).
    When the value in the merchant field (e.g., Lidl Prague) contains the text string Lidl, I want to assign the category value grocery store, etc.
    I used the code from paragraph 6.1 and added the List.Accumulate function, but it does not work.
     

    Replace = 
    List.Accumulate (
      { 0 .. List.Count ( FindReplace[Find] ) - 1 }, 
      Source, 
      ( state, current ) =>
        Table.ReplaceValue (
          state, 
          each [Merchant], 
          each if Text.Contains ( [Merchant], FindReplace[Find]{current}, Comparer.OrdinalIgnoreCase ) 
                  then FindReplace[Replace]{current}
                  else "Other", 
          Replacer.ReplaceText, 
          { "Merchant" }
        )
    )

    Any idea how to proceed?
    Thanks in advance.
    Petr

    Reply
  7. THANK YOU!!! I searched for the bulk replacement solution everywhere and only you provided the solution and it worked fantastically. Your solution and the way you walk through it is absolutely fantastic, so concise, so accurate and right to the point. This should be 5 star tutorial.

    Reply
  8. Ι am really glad to read this weblog posts which consists
    of plenty of useful information, thanks for providing thesе kinds of statistics.

    Reply
  9. Hi Rick,

    Hope you can help. I have a translation table with 2 columns (ColumnName, replaceValue). What I need to do is.. if table column name = to ColumnName then replace null with replaceValue. Essentially, if columnName=xxx then replace null with yyy. xxx and yyy is in a table. Thank you

    Reply
  10. Hello! I find myself needing to combine several functions and just can’t figure out how to organize them properly. Some rows in my “Summary” column contain the word “Misc.” then any one of a series of words like “Tables” or “Image Descriptions” then “Issue ||” (or “issue ||”). Since there aren’t any wildcard searches in Power Query, I’m trying to set something up to say that if a row contains both the “Misc.” and I-don’t-care-what-case-it-is “Issue ||” then I want to change the “Issue ||” part to “Issue: ”
    So, that’s looking for one value and if that value is found, looking for another value in a case-insensitive way, and if that second one is found, replacing that second value with a third value and just leaving the data alone otherwise.
    OR it’s looking for any one of a set of 10 or so values and if a particular one is found changing that particular one so that it ends with a colon instead of a double bar.
    I can’t just replace the || with a colon because there are other ||s that I need to keep. And I don’t want to just replace all “Issue ||” or “issue ||” with “Issue: ” in case that word is used in a context other than it being one of my specific Misc. instances.
    Nothing I’ve tried has worked. Please help!! Thank you ever so much. Your videos and blog are generally extremely helpful, just this situation has me flumoxed.

    Reply
  11. Your site has been a gift! It’s great to be able to learn from someone who has explored PowerQuery so thoroughly. Thanks!

    I have a question related to the subject or perhaps I missed something. How can I do a conditional statement based on a different column than the one I am changing? For example, if ColA’s value = ‘foo’ then replace ColB’s value with ‘foo’ else ColB’s value remains the same.

    ColA ColB
    ———————
    foo bar
    bar bar

    would become

    ColA ColB
    ———————
    foo foo
    bar bar

    Thanks!

    Reply
  12. The only way I can get this to work using a translation table is by using Table.Addcolumn (as you show in section 5), placing the new values in there, deleting the old column and renaming the new column. Simply replacing values in the existing column does not work for me. No error messages, it simply does not happen. I ran into that in the past following instructions identical to yours from other blogs. I am at a complete loss but have resigned myself to using the Addcolumn and two extra steps simply to get the work done. Unfortunately, I have a lot of these in some of my tables.

    Reply
    • That’s terrible. Have you checked whether your replace function uses Table.ReplaceValue or Table.ReplaceText. It may make a difference which one you choose.

      Can you share an example?

      Reply
      • Hi Rick! You were spot on again!! Since the original cell contains a “null”, the Table.ReplaceText didn’t recognize it as a text and nothing happened. I just replaced the function by Table.ReplaceValue and it worked like a charm!!! So happy! Thanks gain!

        Reply
  13. Dear uncle Rick, I have a column with nested tables. I need replace some text that exist inside that nested table, how can I do that?

    Reply
    • hello Raziel,

      You can easily access your date by using double column references, example [Column with nested values][Colum name in your nested table].
      The other logic is quite similar, adding columns or replacing values.

      Reply
  14. Brilliant site. So helpful. Just wondering, is there a way of using something like a MID function to replace the Values?

    I have a column where it has blanks (nulls), and in the column next to it there is the information I need, as the first 3 characters. In excel I just use the MID function to pull across the first 3 characters of that cell.

    So, we have a code like ABC or 911, but some cells are blank. In the column next to it has more of a description like ABC-Alphabet or 911-Emegency.

    To fill in the blanks I want to pull just the first 3 characters from the second column.

    Thanks!

    Reply
    • Hey Ryandx77,

      You can use the function: Text.Start( [Column], 3 ) to get the first three characters.

      Hope that helps !

      Rick

      Reply
  15. I want to match an columnCTable1 with an id columnATable2 and if true, then replace columnBTable1 with content of columnBTable2. But i cant quite get my head around it, im thinking about using listcontains in an if statement maybe.

    Reply
  16. Thank you for this, it has been immensely helpful in several queries I’m working on. I was wondering if you might be able to assist me with a special case I’m working on in a similar vein to method 6.3. The biggest difference I have is that rather than have one Replacement table, I have 40 different Lookup tables which may potentially need to be referenced for the replacement value.

    My hope is that I could add in a column which lists the lookup table which needs to be referenced, and then use the value from this column as the table reference, but I can’t seem to get it right

    So instead of this:

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

    it would look something like:

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

    where the LookupTable column has the value of the table that should be pulled from. Do you have any thought on how this could potentially be accomplished?

    Thank you again for your original posting and any other help!

    Reply
    • Hi James,

      I had a similar issue before when trying to make a dynamic function.

      Try replacing the column reference (ReplacementTable[Old]) with the function:
      Table.Column. The Table.Column function accepts as first argument the table, and as second argument the column name. You’ll be able to make your formula work in that way I imagine.

      Regards,
      Rick

      Reply
  17. Hi Rick
    I have a question not being a “M” person, if I use the formula from your Translation Table Example:-

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

    I get an Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an ‘each’ expression?

    However if I use the next formula that creates a new column it works.
    I am curious as to what causes this error
    Can you shed any light on this please?

    Reply
  18. I liked the text.replace 7 list.accumulate solution for bulk replacement.

    I have also seen your lesson on case-insensitive solution but with a manual replacement.

    Is there a way to use the bulk replace code but still use the Comparer.OrdinalIgnorecase function to make the bulk replacement case-agnostic?

    Reply
  19. Another way to solve for conditional replace (a bit more simplistic than the other conditional options described above:

    = Table.TransformColumns(
        #"Changed Type", 
        {"FieldBeingChanged", each if _ = null then "New Value" else "Old Value"}
    )
    
    Reply
    • Hey StunDexim,

      Thanks for the addition, that one is definitely a good way to change values in a column too! Note that you can’t refer to other columns in the if statement. You can change the ‘current’ value using the underscore and a transformation, or you can add a text yourself. But you can’t return other column values outside the scope of the Table.TransformColumns function.

      Cheers,
      Rick

      Reply
  20. Great post! Thanks for share.

    I could solve the case for multiple collumns “replace” base on below code using an function to convert milleseconds to datetime:

    = Table.TransformColumns(
       #"Expanded plan",
       {
         { "accession_date", each fConvertMillisecondsToDate(_) },
         { "request_date", each fConvertMillisecondsToDate(_) }
       } )

    Thanks.

    Reply
    • Hi Lúcio,

      You are absolutely right, that’s a great way to perform multiple transformations.

      It would have been great to simply input a list of columns and apply the same transformation to all, but by repeating it like in your example you achieve the same with a bit more code. The only downside is, with Table.TransformColumns you can’t reference other column names. Which is what many of the examples require.

      Thanks for sharing !

      Reply
  21. Thank you for a great guide Rick! I am looking for a way to replace values in a single column based on the value in another column and then doing this multiple times with many pairs of columns in a single step (I have no idea if this is possible). Right now I am using your “3. Conditional Replace: Single Column” many times.

    Example: I get two columns of data from a sensor. column1 is the actual measurement data and column2 is a measurement checking the validity of the first column. If the number in column2 is below 100 or above 500 then the data in column1 is replaced with null. This works just fine.

    My problem (annoyance) is that I have A LOT of sensors and don’t want to create a new step for each sensor. Is there a way to combine all the steps into a single step since the condition is always the same, only the pair of columns changes.

    Reply
    • Hey Magnus,

      I believe it would be good for you to unpivot your data, so that all the sets are next to each other in 2 columns. You can then apply a transformation based on a single condition, and pivot the columns back after.

      It will take some experimentation to get the right setup. Hope this gives you an idea how that could work!

      Rick

      Reply
  22. Great article! I’m trying to implement Bulk Replace Using Translation Table to replace a subset of characters and I’m having an issue due to the nature of my translation table. If my looks like this:
    Old New
    CyberSte Cyber One
    Cyber Cyber One

    This works as expected for
    John Smith – Cyber becomes John Smith – Cyber One
    But for Jane Carter – CyberSte becomes Jane Carter Cyber One One

    Any thoughts on how I can get it to use just the first match from the table?

    Reply
    • Sorry – that didn’t look too good.
      for my translation table

      Old Value: CyberSte | New Value: Cyber One
      Old Value: Cyber | New Value: Cyber One

      Reply
      • Les Z,

        That’s a difficult one. Perhaps you’d be able to add a space behind every word just during the replacement. And then make sure you replace ‘Cyber ‘ instead of ‘Cyber’. It really depends on all the matches in your checks. Can’t give a definite answer without delving into the dataset.

        Hope the suggestion helps,

        Rick

        Reply
  23. For the unknown function…
    Replace the cryptic M-Code with this descriptive code:

    (OrigVal, TestResult, NewVal) => if TestResult then NewVal else OrigVal

    //cryptical code for:

    (x,y,z)=> if y then z else x

    drives me crazy. IMHO...It's good for homework you'll never see again, but isn't easily maintainable or understood, long term.

    Reply
      • (x,y,z) must be read as:

        (columnvalue, condition, newvalue ) =>  
        if condition then column else newvalue

        Example: Convert date to YYYYMM for 4 date columns in 1 step:

        = Table.ReplaceValue(#"Reordered Columns1",
        
        each true,   /* this is the condition, the y in x, y, z. */
        null ,       /* this is the new value, the z in x, y, z. */
        (columnvalue, condition, newvalue ) =>  
        if columnvalue = null 
          then "" 
          else Text.From( Date.Year( columnvalue ) * 100 + Date.Month(columnvalue)),
         {"StartDate", "EndDate", "AssignmentsNEW.StartDate", "AssignmentsNEW.EndDate"}
        /* this is the column value, the x in x, y, z. but then materialized */
        /* as e.g. [StartDate]. It processes the x and y and the */
        /*  replacement formula for every column in the list */
        )

        It seems internally an try/otherwise is applied. This means that if there is an error in your formula after the => then nothing happens, the original value will be returned.
        In this example wanted to apply the replacement on all column values of all columns in the list. That is why I used “each true” as condition.
        The new value I left null. But you could put in value e.g. “None” and then use this new value in the formula:

        Each true,
        5,
        (columnvalue, condition, newvalue ) =>  
        if columnvalue = null then newvalue else ….

        If you want to make it depend on another column of the same row then prefix it with “each” and create your new value

        Each true,
            /* this is rubbish, but just for the idea */
        Each if [OtherColumn] = "A" then "6" else "7", 
        (columnvalue, condition, newvalue ) =>  
        if columnvalue = null then newvalue else ….

        It is also a nice way to pass a column value that you can use in your replacement formula (after the => part that is).

        Looking forward to more examples.

        Reply
  24. Hi Rick, thanks for your great post. Maybe by the time I’m writing this you have figured it out yourself, but in case not. The (x,y,z)=> if y then z else x is a function definition that is given instead of Text.Replace. It is just saying that if the value of y is equal to true, return z and otherwise x, keep in mind that [Income] > 50000 can either be true or false.

    Reply
  25. This was fabulous!!!
    I could actually follow for a change.
    Question: How do I do this “5. Bulk Replace Using Translation Table” for multiple columns (not only 1 column Consumer_ID)?

    Reply
  26. Hello Rick,
    your article here helped me understand so many things now about List.Accumulate — thanks!

    I hit a small snag that I can’t seem to figure out when attempting option #5. Here is my syntax, very similar to yours:

    =Table.ReplaceValue(
       #"Some Source",
       each [PType],
       List.Accumulate(
          Table.ToRecords(Path2Category),
          #"Some Source"[PType],
          (state, current) => Text.Replace(state, current[Key], current[Value])
       ),
       Replacer.ReplaceValue,
       {"PType"}
    )

    I got the following error:
    Expression.Error: We cannot convert a value of type List to type Text.
    Details:
    Value=[List]
    Type=[Type]

    I would appreciate some help here. Thanks!

    Reply
  27. For the non-understood function, Table.ReplaceValue doesn’t care what the function is, only the parameters. It passes three parameters in:
    value comes from the second argument,
    old comes from the values in the column list given as the last argument,
    new comes from the third argument.
    Check out the docs for Replacer.ReplaceValue to see the similarities. You can provide your own function instead. Notice when using Replacer.ReplaceValue the end result always has an undefined type – any. That’s because the result of Replacer.ReplaceValue is of type any. You could write your own type-friendly version like this:

    (value as nullable number, old as nullable number, new as nullable number) 
        as nullable number =>
    if value = old then new else old

    which you won’t be surprised to know works for numbers

    Reply
    • Chris,

      Thanks for adding this. I’m starting to get a slight feeling for what’s happening, although I can’t say I’m fully confident yet. I will experiment some with the ideas you post. Appreciate it!

      Rick

      Reply
      • Chris is correct.

        Replacer.ReplaceValue has three arguments, hence the x, y, z. Whatever function you choose to use in place of Replacer.ReplaceValue will take (and be provided) three arguments. The first will be the current value from whatever column is being processed in the final list argument of Table.ReplaceValue. The second will be whatever value is provided by the second argument of Table.ReplaceValue, and the third will be whatever is provided as the third argument to Table.ReplaceValue. Neither of the last two have to be any particular type – they can be booleans from a condition evaluated in the Table.ReplaceValue arguments, or they can be anything else, such as literals or field values; it just depends on what your function needs and how you want to write it.

        Reply
        • Thanks Rory,

          Your explanation helped me a lot to write my own replacer function to convert multiple data and time columns to local timezone. Before I had to add a new line for every column. Now I have only one line for multiple columns and makes the code much more readable.

          I used the following code with the custom function “DateTimeToLocal”:

          Table.ReplaceValue(
          	Source,
          	"Dummy Search",
          	"Dummy Replace",
          	(OrgVal, SearchVal, ReplaceVal) => DateTimeToLocal(OrgVal),
          	{"Column1","Column2","Column3","Column4","Column5"}
          )
          Reply
  28. Great post! I modified #5 from Replacer.ReplaceText to Replacer.ReplaceValue to produce a column with shortened names and abbreviations. The result is more user friendly for subsequent steps in our process. Couldn’t have done it without your help!
    Thank you!

    Reply
    • That’s fantastic, glad to hear it worked Scott. Matter of fact is I didn’t understand the differences before researching it either. Glad it’s helping you get a head start!

      Cheers,
      Rick

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.