In this blog post, we provide a detailed tutorial to dynamically replace multiple column values in one step in Power Query. With some M programming, we can use logic to replace multiple column values in Power Query in Excel or Power BI.
Manually replacing values in Power Query, one by one, works well for a few changes but this approach falls apart when the list of values that need to be replaced grows longer, both in terms of performance and maintenance.
Fortunately, there is a way to dynamically replace values using a single transformation step, which can significantly streamline your queries. Read along as we demonstrate how to use Power Query’s M language to replace multiple values in one step.
The Inefficiency of Manual Replacement
Power Query offers a straightforward interface for replacing values. All you need to do is right-click on a column header and select Replace Values, which will present a user interface to type in the value to replace and what it should be replaced with.
However, each time a value is replaced, it adds a new step to the query. This means that if you’re replacing 10 or more values, this quickly becomes cumbersome – not only does it clutter the Applied Steps pane, but it can also degrade performance.
To address this, we can create a dynamic replacement process using a combination of a conditional statement and M code.
Use Case: Expanding Province Abbreviations
Consider a dataset of store locations across Canada. As an analyst, the only dataset we have access to includes a Province column containing standard two-letter abbreviations (i.e., ON, AB, SK). For reporting and readability purposes, you may want to convert these abbreviations into their full province names.
One approach is to create a new column using a conditional statement to map each abbreviation to its corresponding name. Here’s how to do that:
- Navigate to Add Column tab and select Custom Column.
- Name the new column something like Province Name.
- Use an if…else statement to map each abbreviation:
if [Province] = “AB” then “Alberta”
else if [Province] = “BC” then “British Columbia”
else if [Province] = “ON” then “Ontario”
else if [Province] = “SK” then “Saskatchewan”
…
else [Province]
This results in a new column with full province names based on the original abbreviations. While this is effective, it still adds a new column and doesn’t replace the original values in place.
However, if the original column will not be used in reporting, it is just adding clutter to the data model and the values of this column should be replaced instead of creating a new column.
Dynamic Value Replacement Steps
Instead of creating a separate column, we can use Table.ReplaceValue with a dynamic condition to replace values directly within the existing column. We can do this all in a single step.
If you’re new to Power Query, and prefer to do as much data transformation as you can using the Power Query editor’s user interface, you can start by right-clicking the original Province column and choosing Replace Values.
In the replace values dialog box, you can enter any of the values you wish to replace (i.e., replacing “AB” with “Alberta”. This will replace the AB values with Alberta and create a new step under the Applied Steps pane.
Adjusting the M Code
Next, we can adjust the M code that Power Query wrote based on our last step using the user interface using the Advanced Editor. If you are an advanced M programmer, you may choose to go right to this step and write out the entire transformation step.
From the Home tab, navigate to Advanced Editor to launch the code editor inside of Power Query Editor.
You will notice that our last step (#”Replaced Value”) uses the Table.ReplaceValue function. Let’s break down what is in this function currently to figure out how we can adjust it:
- The first argument in this function is a reference to the previous applied step (in our case, #”Changed Type”). This is standard with all M functions of this nature.
- The second argument (“AB”) is the value we wish to replace.
- The third argument (“Alberta”) is the value we with to replace it with.
- The fourth argument (ReplaceText,{“Province”}) tells the Power Query engine what column this should be applied to.
So, let’s start modifying this code to replace all of the values in the Province column, from abbreviations to full province names.
The first thing we will do is to replace the second argument (“AB”) with each [Province]. This tells the Power Query engine that we want to replace each value inside of the Province column instead of just replacing “AB”.
Next, we can replace our third argument for a simple if…else logic:
each if [Province] = “BC” then “British Columbia”
else if [Province] = “AB” then “Alberta”
else if [Province] = “SK” then “Saskatchewan”
else if [Province] = “MB” then “Manitoba”
…
else [Province]
That’s it! We’re done. Putting it all together, our M code should look something like this:
let
Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,
{
{“Province”, type text},
{“Store”, Int64.Type},
{“Sales_Amount”, Int64.Type}
}
),
#”Replaced Value” = Table.ReplaceValue(#”Changed Type”,
each [Province],
each if [Province] = “BC” then “British Columbia”
else if [Province] = “AB” then “Alberta”
else if [Province] = “SK” then “Saskatchewan”
else if [Province] = “MB” then “Manitoba”
else if [Province] = “ON” then “Ontario”
else if [Province] = “QC” then “Quebec”
else if [Province] = “NB” then “New Brunswick”
else if [Province] = “NS” then “Nova Scotia”
else if [Province] = “NL” then “Newfoundland and Labrador”
else if [Province] = “PE” then “Prince Edward Island”
else [Province],
Replacer.ReplaceText,{“Province”})
in
#”Replaced Value”
This version of Table.ReplaceValue evaluates each row in the Province column and replaces its value based on the condition specified. The each keyword ensures that the logic is applied row by row. The conditional logic can be extended to cover all relevant values.
Why This Approach Is Better
There are several advantages to using this method:
- Efficiency: Reduces the number of steps in the query, resulting in better performance and easier maintenance.
- Scalability: Ideal for replacing long lists of values without repeating the same transformation multiple times.
- Clarity: Keeps the transformation logic centralized in one location, making the query easier to read and debug.
Even with just 10 values, such as Canada’s 10 provinces, this approach significantly simplifies the data transformation process. And in cases where the list of values is much longer, the time and performance savings become even more substantial.
Wrapping It Up
Dynamic value replacement in Power Query is a powerful technique that can greatly enhance the efficiency and maintainability of your data transformations. By applying conditional logic directly within the Table.ReplaceValue function, you can simplify your queries and reduce unnecessary complexity.
This method is especially useful when working with standardized codes, such as province abbreviations, product SKUs, or region codes, and needing to present more meaningful or readable values in your reports.
If you’re building reusable and scalable data models, integrating this technique into your Power Query toolkit is well worth the effort.
Need Help Getting Started With Power BI?
Our Microsoft Certified consultants can help with the implementation of Power BI in your organization
