Replace Value For Matching Text In Power Query - Enterprise DNA

Replace Value For Matching Text In Power Query

No comments

If you’ve ever had a requirement to perform multiple exact text match replacements, there’s a good chance you’ve performed a series of replace value steps. But as always, there are multiple and easier ways to achieve the same result. You can watch the full video of this tutorial at the bottom of this blog.

In today’s blog, I will demonstrate the M function Record.FieldOrDefault for this specific use case. 

Steps To Replace Value For Multiple Exact Match Texts 

Navigating the Data Set

Let’s start by opening the Query Editor to access the Sample data and Replacements record.

Inside the table, we see that not all of the text strings are left aligned.

Steps To Replace Value For Multiple Exact Match Texts 

When we move to the Replacements, we can examine our record and understand why this method can only work on replacing exact text matches. 

A record is a set of fields and each field has a name-value pair.

The field name is required to be a unique text within the record with Record.FieldOrDefault. 

Steps To Replace Value For Multiple Exact Match Texts 

The field name will be used to identify the thing that we’re looking for, while the field value that will be used for the replacement can be of any type. 

Replacing the Values

Let’s see how it works. 

First, I will go to the Solution query, and on the Transform tab, I will select Replace Values.

Steps To Replace Value For Multiple Exact Match Texts 

A pop-up window will show up, and I will press OK

Steps To Replace Value For Multiple Exact Match Texts 

This generates the bulk of the M code form. So, all I need to do is update the syntax inside the formula bar. 

Steps To Replace Value For Multiple Exact Match Texts 

From the formula, we can see that it calls our source table as the first argument and the thing that we’re looking for as the second argument. The arguments are denoted by these double quotation marks (“ “).

Steps To Replace Value For Multiple Exact Match Texts 

The thing I am looking for is the value inside column one, so I will type each [Col1] for the first argument. Next, it wants the thing to replace the bot, and this is where I will use that record function. 

I will type each Record.FieldOrDefault. Then, it wants the record or the Replacement and finally, the thing that we’re looking for. I can find that inside column one so I will copy and paste [Col1] from the first argument to reference back. 

If the value isn’t found within the record, I want it to return what’s currently there. Thus, I will paste [Col1] again to reference to column one in case the value is missing. If I omit this final parameter and the item is not found inside the record, a null will be returned Instead.

These changes make our formula appears like this.

Let’s confirm by clicking the Check Icon on the left of the formula bar.

The result is shown in the image below.

Each text value inside column one that had a matching field name in the replacement record has now been updated.

***** Related Links *****
Microsoft Flow String Functions: Concat And Replace
Data Validation Using IsMatch And Variables
Extended Date Table Power Query M Function

Conclusion

In this blog, I demonstrated how to do multiple exact-match text replacements in Power Query. By using the M function Record.FieldOrDefault, you can save time by reducing the steps needed to achieve the same results. 

All the best,

Melissa de Korte

Leave a Reply

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