Replace Values in Power Query M (Ultimate Guide)

Updated on

August 18, 2024

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

Fundamentals
Replace in Multiple Columns
Conditional Replace: Single Column
Conditional Replace: Multiple Columns
Bulk Replace Using Translation Table
Replace Values: Case-Insensitive
Replace Substrings in Bulk
Conclusion

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:

Argument	Description
1. Table as table	This is the table where values are replaced
2. Old Value as any	The value that you want to replace by a new value
3. New Value as any	The value you want to replace the old value by
4. Replacer as function	One of replacer functions: – Replacer.ReplaceValue – Replacer.ReplaceText
5. Columns to search as list	Column 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:

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:

change the data types of all the selected columns to text
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:

add a custom/conditional column
build your if-condition and return the correct values
remove the old column
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.Accum u late 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!

Recommended Reading…

Power Query Foundations

Beginners Guide to Power Query

List Functions (200+ Examples)

Text Functions (150+ Examples)

Creating Tables (40+ Examples)

If Statements in Power Query

Generate the Ultimate Date Table

Advanced Topics

Master List.Generate with Easy Examples

Deep Dive into List.Accumulate

Create Calendar with a Dynamic Language

Replacing Values (Beyond the User Interface)

Latest from my blog

Power Query

Understanding Semi Joins in Power Query M

September 8, 2024

Power Query

Creating a 445 Calendar (incl 454, 544) in Power Query M

August 14, 2024

Power Query

Using Variables in Power Query M

July 4, 2024

Power Query

Convert Unix Timestamp to Datetime in Power Query M

July 3, 2024

Power Query

Extracting Year, Month, or Day from a Date in Power Query

June 20, 2024

Power Query

Dynamic Group By Operations in Power Query M

June 19, 2024

71

Comments

Adriano

August 27, 2024 at 8:42 PM

Hello Rick,
my approach for a value-replacement in multiple Columns besides the usage of list.accumulate or pivoting is like this

let
Recordlist = Table.ToRecords(SourceTable), //Sourcetable for Replacement as List of Records
TargetFieldList = {“Col1″,”Col2″,”Col3”}, //ColumnNames for Replacement
oldVal = 1, //LookFor Value
newVal = 42, //Replacement Value
transformedRecords = List.Transform(Recordlist,each Record.TransformFields(_,List.Transform(TargetFieldList,each {_, (_) => if _=oldVal then newVal else _ }))),
returnTbl = Table.FromRecords(transformedRecords) //Rebuilding the Table
in
returnTbl
Reply
Mauricio Alejandro

July 9, 2024 at 9:33 PM

Rick : could you please clarify in the case you define your own function as a replacer function with the following code :
Table.ReplaceValue(#”Removed Other Columns”,
each [Income] >50000,
each 2,
(x,y,z) => if y then z else x ,
{“Kidhome”, “Teenhome”})
Which is x, y and z in the numbers above?. Thanks Mauricio Alejandro
Reply
Uwe Heuer

June 27, 2024 at 11:51 AM

Hi,
this is a very good article and I like it very much. Regarding the replacement of values based on a mapping table I ran into performance issues (I had ~40k rows). ChatGPT than gave me the hint to use a merge query which worked very well.
Reply
Lachelle Mcgary

June 17, 2024 at 12:59 AM

It’s an awesome paragraph in support of all the internet viewers; they will obtain benefit from it I am sure.|
Reply

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

Hi there,

Give this code a try:

Table.ReplaceValue(
  #"Added Custom2", 
  each [#"F&O"], 
  each if [#"F&O"] <> "" then "F&O" else [#"F&O"], 
  Replacer.ReplaceText, 
  {"F&O"}
)

Reply

sri ram

May 1, 2024 at 7:42 AM

Hi Rick,
A particular column in power query has a set of values which are to be replaced by a single value. How?
Reply
El_Conquistador

April 16, 2024 at 4:03 PM

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
- Rick de Groot
  
  April 29, 2024 at 11:28 AM
  Hi there! Have you thought of using the Text.BeforeDelimiter function?
```
Text.BeforeDelimiter( [YourColumn], "NUL-" )
```
  Instead of removing everything from -nul-, it retrieves everything before it.
  
  You can read more about it here: https://powerquery.how/text-beforedelimiter/
  Reply
David A Beans

March 11, 2024 at 10:55 PM

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
Byron

January 12, 2024 at 11:23 PM

Say you have a column with 2 identical values. How do you go about replacing only the 1st or 2nd value. IE
a
a
to
b
a
Reply
Nico Prillwitz

January 2, 2024 at 5:38 PM
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

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

Petr

September 7, 2023 at 6:38 PM
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
Phronesis

August 25, 2023 at 8:38 AM

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
mitigated

June 19, 2023 at 10:02 PM

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

April 20, 2023 at 10:35 PM

bravo 👏👏👏
Reply
Jack

February 17, 2023 at 3:33 AM

Hi Rick

Is it possible to replace a cell with the value in the cell below

In the example attached, if the Cell = [split], replace it with the value in the cell below

image:
https://mega.nz/file/hCsURa4A#JZtb7hEZq9vunFJMNFbzkZmMCgJLV6O4-yhTe5C0L5k
Reply
Deepak

January 2, 2023 at 12:36 PM

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
LC

October 18, 2022 at 6:32 AM

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
Michael

September 29, 2022 at 5:19 PM

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
- Rick de Groot
  
  October 25, 2022 at 12:54 PM
  Hey Michael,
  
  You can read more about the conditional logic in this section:
  https://gorilla.bi/power-query/replace-values/#conditionally-replace-values
  
  You should be able to do something like:
```
= Table.ReplaceValue(
     #"Changed Type",
     each [ColumnB],
     each if [ColumnA] = "foo" then "foo" else [ColumnB] ,
     Replacer.ReplaceValue,{"ColumnB"}
 )
```
  Enjoy!
  Reply
Michael

September 5, 2022 at 6:50 PM

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
- Rick de Groot
  
  October 25, 2022 at 3:17 PM
  
  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
  - Michael
    
    February 17, 2023 at 4:41 PM
    
    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
Raziel

August 27, 2022 at 8:28 PM

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
- Stijn D.
  
  October 30, 2022 at 6:04 PM
  
  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
Ryandx77

August 8, 2022 at 6:59 AM

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
- Rick de Groot
  
  August 27, 2022 at 8:21 PM
  
  Hey Ryandx77,
  
  You can use the function: Text.Start( [Column], 3 ) to get the first three characters.
  
  Hope that helps !
  
  Rick
  Reply
Ilker

July 14, 2022 at 3:11 AM

It is awesome, thank you so much!
Reply
Michael

July 5, 2022 at 10:37 AM

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
James

June 22, 2022 at 10:42 PM
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
- Rick de Groot
  
  June 24, 2022 at 11:23 AM
  
  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
Scott

May 18, 2022 at 3:00 AM
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
- Olivier op ten Noort
  
  November 24, 2022 at 8:52 AM
  
  Hi Scott, you seem to be missing another ) after ReplacementTable
  Reply
Monoj Chakraborty

May 10, 2022 at 5:01 PM

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
StunDexim

April 21, 2022 at 6:51 PM
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
- Rick de Groot
  
  May 10, 2022 at 9:50 PM
  
  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
Lúcio A

March 19, 2022 at 1:22 AM
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
- Rick de Groot
  
  June 4, 2022 at 3:51 PM
  
  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
LUIZ ANTONIO

February 23, 2022 at 1:58 AM

4.2
x = original value

y = match condition (true or false)

z = replacement value
Reply
- Quyet
  
  July 22, 2023 at 10:27 PM
  
  Can you please share a document about this mentioned concept?
  Reply
Magnus

February 8, 2022 at 11:20 AM

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
- Rick de Groot
  
  May 10, 2022 at 9:52 PM
  
  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
Les Z

February 5, 2022 at 2:28 PM

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
- Les Z
  
  February 5, 2022 at 2:32 PM
  
  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
  - Rick de Groot
    
    June 4, 2022 at 3:55 PM
    
    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
  - Rick de Groot
    
    June 6, 2022 at 8:06 PM
    
    I just added a new section (6.4) to the report, it should work for your example 🙂 enjoy!
    Reply
Ron Coderre

February 1, 2022 at 8:49 PM
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
- Rick de Groot
  
  June 4, 2022 at 4:07 PM
  
  Thank you!!
  Reply
  - Robert F. Lamping
    
    November 23, 2022 at 10:18 PM
    (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
Reza

January 19, 2022 at 3:42 PM

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
- Rick de Groot
  
  June 4, 2022 at 4:07 PM
  
  Thanks for your explanation of what happens here Reza ^^
  Reply
Marinda

January 6, 2022 at 10:38 AM

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
Khaireel

December 24, 2021 at 10:54 AM
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
Chris

December 10, 2021 at 12:52 PM
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
- Rick de Groot
  
  December 12, 2021 at 8:51 PM
  
  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
- David
  
  September 12, 2023 at 12:56 PM
  
  I do fully not agree.
  
  (x,y,z)=> means in fact
  (currentValue, isConditionTrue, replacementValue)=>etc…
  
  Check Example #4 in official documentation.
  https://learn.microsoft.com/en-us/powerquery-m/table-replacevalue
  
  It will all make sense when you read example 4.
  Reply
  - Rory
    
    December 4, 2023 at 9:10 AM
    
    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
    - Serge Leroy
      
      January 31, 2024 at 1:02 PM
      
      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
Scott

December 9, 2021 at 10:10 PM

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
- Rick de Groot
  
  December 12, 2021 at 8:47 PM
  
  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
JeteMc

December 8, 2021 at 5:44 PM

Thank You!
Reply
Annelies

November 3, 2021 at 6:41 PM

You really helped me out of trouble, thanks a lot!
Reply
- Rick de Groot
  
  December 12, 2021 at 8:47 PM
  
  You’re very welcome!
  Reply
anime de romance harem vida escolar

October 15, 2021 at 11:05 AM

Gracias por el capítulo: 3
Reply
- Rick de Groot
  
  October 21, 2021 at 11:04 AM
  
  Mi placer!
  Reply
Taras Koniukhov

September 28, 2021 at 9:31 AM

Very comprehensive guide.
Thank you, Rick!
Reply
- Rick de Groot
  
  September 29, 2021 at 7:13 PM
  
  My pleasure. There’s so many ways you can use this. Hope this brings some clarity and structure to it!
  
  ^Rick
  Reply
  - Willi
    
    July 28, 2022 at 7:17 PM
    
    Can we use wildcard like find and replace in excel?
    ? for single character
    * for multiple characters
    Reply
    - Rick de Groot
      
      December 5, 2023 at 2:38 PM
      
      You can to some extent as written about here: https://gorilla.bi/power-query/wildcards/
      Reply

Leave a comment Cancel reply

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

About BI Gorilla

BI Gorilla is a blog about DAX, Power Query and Power BI. It was founded in 2018 by Rick de Groot with the goal to provide easy to understand resources to help you advance.

Latest Posts

M Language Function Reference

You can now find everything you need to know on the Power Query M language (syntax, examples, articles etc.) at https://powerquery.how/.

Newsletter

Free your mind, automate your data cleaning. Sign up

©️ 2024 BI Gorilla. Powered by Rocket.net, FlyingPress • Built on theme GeneratePress

Blog Contact Privacy Policy