[How To] Replace Column Values in Power Query

[How To] Replace Column Values in Power Query

Replacing column values in Power Query is one of the most common data transformations when connecting and transforming data in Power BI or Excel. In this blog post, we show you two simple ways to perform this task – follow along!

Introduction

In data analysis and visualization, clarity and accuracy are crucial – it not only helps report readers understand information presented to them, but it also helps build trust between the analyst and the target audience.

Both Microsoft Power BI and Excel offer functionalities within each tool to enhance data integrity. Behind both tools is a data transformation engine, called Power Query, which allows you to seamlessly transform your data prior to building reports.

One such functionality that Microsoft makes available, through Power Query, in Power BI and Excel is the ability to replace column values. This is a powerful feature as data often comes in various formats and with various levels of detail.

Often, datasets contain abbreviations or codes that, while convenient and useful in a database, can detract from the clarity and readability of your reports. For example, having marital status represented by a single-letter code (i.e., M = Married, S = Single, etc.) can be ambiguous and might not serve all audiences.

So, how can you address this in Power BI or Excel? The following section of this blog post walks you through two simple ways to replace column values in Power Query.

Steps to Replace Column Values in Power Query

As is common with most Microsoft products, Power Query allows you to replace column values in a couple of different ways:

The first way is to use the Replace Values feature from the Home tab in Power Query:

Step 1

Select the column that you want to replace values in Power Query.

Step 2

From the Home tab in Power Query, navigate to the Transform section and click the Replace Values button. This will open the Replace Values dialog box.

Step 3

In the Value to Find field, enter the value that you wish to replace. In our example below where we wish to replace the marital status from the code “S” to the word “Single”, the value we enter in the Value to Find field is “S”.

Step 4

In the Replace With field, enter the new value you wish to display in the column. In our example, this would be the word “Single”.

Now we could click OK and be done with it. But, the way we have done this so far, Power Query will search for any instance of a capital letter S. This means that if the value “SS” exists in our column, Power Query will replace this value with “SingleSingle”. This is not the desired outcome.

To prevent this outcome from happening, expand the Advanced options menu in the Replace Values dialog box, and check off the option to Match entire cell contents. Now, Power Query will search for instances where “S” matches the entire contents in each cell, so rows that have “SS” would not be replaced.

Now we can click OK and our “S” values will have been replaced with the values “Single”.

The second way to perform this task, is by going to the Transform tab and clicking the Replace Values button under the Any Column section. This will open up the Replace Values dialog box and the steps to replace the column values in Power Query would be the same as described above.

The main difference between replacing column values from the Home or the Transform tab, is that in the Transform tab the Replace Values button has a down arrow that gives you one additional option – to replace errors. This option can be useful if you want to replace column values with errors for a specific value.

A Note About Performance

Power Query is well equipped to allow you to replace column values, but it is important to remember that each step added in the preparation of your data for analysis impacts your report’s performance (i.e., how quickly it loads).

If you are working with a large dataset and you have many column values to replace, you may find that your reports are not quick to load up and data refreshes may take longer.

To avoid sluggish performance, it is always best practice to perform data transformations as far upstream as possible. This means that if you can replace the column values right at the source of the data, for example in a SQL Server View, it is recommended to do so.

Conclusion

Replacing column values in Power Query is a simple, yet crucial step in preparing your data for analysis and visualization in both Power BI and Excel. And, like renaming columns, it is one of the most common data transformations in Power Query when analyzing data in Power BI or Excel.

Need Help Implementing Power BI at Your Organization?

Our Microsoft Certified consultants can help

Book a meeting

Leave a Reply