Power Query: Replace Multiple Column Values in Single Step
Stop doing multi-step Power Query transformation when the same can be done in single step!
When discussing solutions in Power BI, people often solve issues either through SQL at the source or using DAX in Power BI Desktop.
However, not everything should be done that way. Developers often overlook Power Query or M Language transformations, which are integral parts of Power BI.
Certain transformations can be done in the Power Query Editor window of Power BI and we will discuss one such transformation here.
When we load raw data into Power BI, we often observe anomalies that require transformations before the data is ready for reporting. One such transformation is replacing unwanted values/characters in a column with new ones.
Let me tell you, this transformation is actually easy and can be done using multiple transformations via the "Replace Value" feature. But that's not the goal of this post. Why unnecessarily use multiple transformations when you can do the same in one single step, right?
But before I start explaining the single-step transformation, let’s first understand the problem statement.
Problem Statement
You have a Sales table in your model, and the “Item Type” column has some old values that you want to replace with new ones. It is also observed that some values with more than one word are separated by a hyphen (“-”), which we don’t want. You need to replace “-”(hyphen) with a “ ” (space).
Here is the summary of the changes that need to be done:
Replace “Clothes” with “Garments”
Replace “Meat” with “Non Veg Food”
Replace “Cosmetics” with “Beauty Products”
Replace the hyphen “-” with a “ ” (Space)
Okay, as we now understand the task at hand, let’s get started with the transformation.
The Usual Approach
I also call this method the novice one because it is not the optimized way of doing multiple transformations as i had said earlier. I’d not recommend doing it this way, but many still do, making the transformations in Power Query a bit messy.
But for the sake of comparison, let’s see:
Step 1: Right click on the “Item Type” column header and select Replace Values from the list.
Step 2: In the Replace Values transformation screen, type “Clothes” in Value To Find text box and “Garments” in the Replace With text box and Click OK.
Step 3: Once you hit the OK button, you will see that all the “Clothes” values in that column are now replaced with “Garments”. You will see a new step added in the transformation window for “Replaced Value”. Rename it by right-clicking to “Replaced Clothes” so that it makes sense.
You can also see that a Power Query transformation script (M language) is generated for this, which you can see in the formula window.
Step 4: Repeat steps 1 to 3 for replacing other values. This is how the screen will look after you are done with the changes👇.
Okay, we are done with our changes and the data looks clean now. But, we also need to see that for doing similar kinds of transformations, i.e., replacing values, we applied the transformation 4 times.
Just imagine doing this where you have more than 10, 20 or maybe hundreds of transformations. This method would become redundant and cumbersome and also make your query transformation load slow.
To overcome this, we need to find an optimized or a better way. So, let’s see that.
The One-Step Wonder: Replacing It All!
Okay, so this is the most optimized way of replacing multiple values. We are going replace all the values and characters in a single step.
Let’s dive into it directly.
As usual, as the first step, let’s add the “Replace Value” step by replacing “Clothes” with “Garments”, so that we have the Power Query template to replace values ready.
Step 1: Create the Replace Value step
Right-click the “Item Type” column header and select the “Replace Values” option from the list. Insert “Clothes” in the “Values to Find” text box and “Garments” in the “Replace With” text box. Once done, you will see the Power Query script generated similar to what we got in method one.
Note: You can also write the script directly as a new step, but for the sake of it, let’s go with this way.
Step 2: Modify the Power Query Script
We will break our set of transformations into two categories: in the first, we’ll replace the values in the column and in the second, we’ll replace characters in each value.
So here, we’ll replace “Clothes”, “Meat”, and “Cosmetics” in first category and the hyphen “-” in the second category.
Now, here is how we need to modify the script👇
= Table.ReplaceValue(#"Replaced Value",
each [Item Type],
each if [Item Type] = "Clothes" then "Garments" else
if [Item Type] = "Meat" then "Non Veg Food" else
if [Item Type] = "Cosmetics" then "Beauty Products" else
if Text.Contains([Item Type], "-") then Text.Replace([Item Type], "-", " ")
else [Item Type],
Replacer.ReplaceText,{"Item Type"})
Let me explain the Power Query script here a bit. For Table.ReplaceValue
, the first parameter is the name of the previous step, here it's #"Replaced Value". Next is the value that we want to replace. Here, instead of "Clothes" that we inserted in the window, we'll replace it with each [Item Type]
, where "Item Type" is the column name. This will tell the engine to check for each of multiple values in that column.
The next argument is the values that we are replacing. Here, instead of “Garments”, we’ve used a combination of if-else statements to replace multiple values. In the else case, we’ve returned the column itself.
As you can also see above, we have added an additional if condition to check if the value contains the character “-”.
We have made use of the Text.Contains
function to check if the "Item Type" column values contain the character "-". If yes, we are making use of the Text.Replace
function to replace the "-" (hyphen) in the column values with the " " (space).
Here is how the table looks after this one-step transformation to replace the values👇:
Bam! We did replace all the values and characters in one single step.🥇
This really becomes helpful in situations where we are dealing with very large datasets having lots of text values and we need to do multiple transformations.
Conclusion
This brings us to the end of this piece on Power Query transformation. I tried to explain how we can replace multiple column values in a single step and it’s going to be super useful.
Have you worked on these transformations in a different manner? I’d love to know your experience.
I hope you enjoyed reading this piece and got something out of it. Do share your thoughts in the comments!
Want to elevate your Power BI and DAX knowledge further, hop here:
👉 YouTube 👉 Medium 👉 BuyMeACoffee