Replace Values in Power Query M (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. The dataset for replacing values

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

1.2. 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 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:

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

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

6. Replacer.ReplaceValue entire cell contents succeeds

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

6. Replacer.ReplaceValue entire cell contents fails

Changing the replacer function to the text version 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.ReplaceValues function. Whereas the unchecked version uses the ReplaceText version.

7. Replacer.ReplaceText Advanced Options

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:

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

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.

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

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.

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

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.

14. Case insensitive replacement table

Imagine you want to replace the values in 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. 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.

4. Bulk Replace List.Accumulate formula

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.

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

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?

11. Case sensitive dataset

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:

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

12. Case insensitive replace multiple items

The result of the replace operation is a list. 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). 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" } }
          )
   )
13. Case insensitive replace multiple items concatenated

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. 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 below transformed dataset:

13. Case insensitive replace multiple items preparation

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

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.

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:

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

// With a 2 column replacement table you can achieve the same with: 
= List.Zip(  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
          )
   )

6.4. 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 _ ), " " )
 )

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!

Recommend Reading

>>> Power Query - Foundations <<<
 List Functions (200+ examples)
 Text functions (150+ examples)
Creating Tables from Scratch (40+ examples)
 Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
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.

46 thoughts on “Replace Values in Power Query M (Ultimate Guide)”

  1. 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
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
    • 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
  11. 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
  12. 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
  13. 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,
        Each if [OtherColumn] = “A” then “6” else “7”, /* this is rubbish, but just for the idea */
        (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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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