Power BI Replace Text Tutorial: Conditional Full & Partial Replacement

by | Power BI

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!

Power BI Replace Text in Column Data set

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.

Power BI Replace Text in Column Data set Rep0lacement List

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.

Power BI Replace Text in Column using Advanced Editor

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

partial text replacements

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.

Power BI Replace Text in Column with Matching Items

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.

Power BI Replace Text in Column with Text.Split

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.

Power BI Replace Text in Column with Text.Combine

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.

Related Posts

Using the DISTINCT Function Effectively in DAX

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.