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.
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.
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.
A pop-up window will show up, and I will press OK.
This generates the bulk of the M code form. So, all I need to do is update the syntax inside the formula bar.
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 (“ “).
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
