This blog will demonstrate how to replace text in Power BI by combining conditional full and partial text replacements in Power Query all in one simple step. You can watch the full video of this tutorial at the bottom of this blog.
In Power Query, you can replace one value with another in a selected column, specific values, or the entire value in a cell. We will use this feature to replace text both fully and partially. So let’s get started!
The image above represents our data, consisting of several text strings. We also have a replacement list you can see below, which contains a look for Value followed by whatever replaces it. Some of the values we want to replace in full and others in part, but both are listed here.
Replacing Values
First, go to the Transform Tab, select Replace Values, and press OK in the Replace Values window above. After that, we’ll open and use the Advanced Editor window below.
Replace Text in Power BI Data set
We want to look for each value in the name column, so we enter each [Name], each next to Source in the fourth line.
Next, we want to perform a conditional replacement depending on what’s in our Replacement list. If we can match the entire string, we want to replace it fully.
And if not, we want to look for partial text replacements. So we continue by entering if List.Contains( Replacements, ,(“Name”).
Now, this won’t work because our Replacements list, as we saw earlier, contains a list with a nested list. And each first item in the list is what we’re looking for.
Thus, we need to update this code and transform our Replacements to List.Transform( Replacements, List.First). We move it to a new line and now have the image below.
Replace Text in Power BI with Matching Items
So we’ve modified our Replacements list, and we’re only keeping the first items, the thing we’re looking for. If that contains whatever is in the name column, we should do a full-text replacement, and we can use ListReplaceMatchingItems to retrieve that replaced value.
So in the following line (6th line), we enter then List.ReplaceItems. It will want a list, so we’ll enter those curly brackets and the Name column and Replacement list inside it. And we can make this case insensitive by putting in Compare.OrdinalIgnoreCase.
This action will return a list with one item, the value to replace it by. To extract that, we can use the item access operator. And, inside those curly brackets, we refer to its first position, 0.
Else Clause
Now the else clause will be similar to the then clause, so let’s copy the previous syntax and paste it into the next line. Remember to put else at the beginning.
Text.Split
Instead of the Name, we need to see if there is a partial match, so we’ll split up the string in the name column. Thus, we can use Text.Split and split that value into spaces. And because it will return a list, there’s no need to add those curly brackets here.
Replace Text in Power BI: Text.Combine
Since it will replace only the matching items, we must assemble all those words back into a single string. Then, I’ll wrap Text.Combine around it and add the space we removed together with a comma.
Finally, we click Done at the bottom of our Advanced Editor window and we get the table below.
Our table, however, is in Type Any. To fix that, we need to change that replacer to ReplaceText instead of ReplaceValue and press that check mark to complete the changes.
***** Related Links *****
Creating A Custom Text Cleaning Function In Power Query
Column From Examples Power BI – Power Query Tutorial
A Quick Overview On Different Power Query Tools In Power BI
Conclusion
With some ingenuity, we created a function that can perform conditional full and partial text replacement based on a list of values. And we completed that replace text in Power in BI process in just a single step!
The key is to create a custom function that performs conditional replacement depending on what’s in our Replacement list. If it can match the entire string, it replaces it fully. And if not, it looks for partial text replacements.
