How to Find and Replace in Power BI

Cleaning up messy data is one of the most common tasks in Power BI, and knowing how to quickly find and replace values is a fundamental skill. Whether you're fixing typos, standardizing categories, or removing unwanted characters, changing values directly in Power BI saves you from repeatedly editing source files. This article will show you the easiest way to find and replace data using the Power Query Editor and also cover a few alternative methods using DAX functions.

Why Find and Replace is an Essential Data Cleaning Step

Working directly with raw data often means dealing with inconsistencies. Your data might look correct at a glance, but small variations can break your visuals, skew calculations, and ultimately lead to inaccurate reports. Regularly using a find and replace function solves several common problems:

• Standardizing Categories: Your sales data might contain product categories like "T-Shirt", "tshirt", and "tee shirt". To analyze sales accurately, you need to group all of these under a single, standardized name like "T-Shirt".
• Correcting Data Entry Errors: Simple typos from manual data entry are everywhere. "New Yrok" should be "New York," and a sales territory listed as "Suth" needs to be corrected to "South". Replacing these errors ensures data quality.
• Formatting for Consistency: You might want to replace shortened state codes like "CA", "TX", and "FL" with their full names ("California", "Texas", "Florida") for more professional-looking charts and tables.
• Removing Unwanted Characters: Data exported from other systems might include unnecessary characters like asterisks, hashtags, or line breaks that you need to remove to make the data usable.

Cleaning these values at the source - inside Power BI - means you create a repeatable, automated process. Every time you refresh your data, your cleaning steps are reapplied automatically, saving you hours of manual work in spreadsheets.

The Easiest & Best Method: Replace Values in Power Query

The most effective place to clean and transform your data is in the Power Query Editor. Transformations made here are applied to the data before it's loaded into the Power BI data model, which is the most efficient and reliable practice. The "Replace Values" tool is your primary method for this.

Step-by-Step Instructions

Let’s walk through a common scenario: you have a column for 'Country' and you need to standardize 'U.S.' and 'USA' to 'United States'.

1. Open the Power Query Editor

From the main Power BI Desktop window, go to the Home tab and click on Transform data. This will launch the Power Query Editor in a new window, where you can see all of your data tables.

2. Select the Column to Clean

In the Power Query Editor, find the table you want to work with from the Queries pane on the left. Then, click on the column header of the data you want to change. For this example, we’d click on the 'Country' column header to select it.

3. Use the Replace Values Tool

With the column selected, there are two easy ways to access the "Replace Values" feature:

• Right-Click Method: Right-click anywhere on the selected column and choose Replace Values from the context menu. This is often the quickest way.
• Ribbon Method: Go to the Transform tab in the ribbon at the top of the window and click Replace Values. You can also find this option on the Home tab.

Either method will open the "Replace Values" dialogue box.

4. Enter the Values to Find and Replace

The dialog box is straightforward:

• In the Value To Find field, type the text you want to get rid of. Let's start with 'USA'.
• In the Replace With field, type the new text you want to use. We'll enter 'United States'.

Click OK. Power Query will immediately scan the entire column and replace every instance of 'USA' with 'United States'.

5. Repeat for More Values

You can repeat the process as many times as you need for the same column. We still need to replace 'U.S.' To do this, right-click the 'Country' column again, select "Replace Values," enter 'U.S.' in the Value To Find field and 'United States' in the Replace With field, and click OK.

Every transformation you make is recorded as a step in the Applied Steps panel on the right. You can click on any previous step to see what the data looked like before that change, making it easy to track and debug your data cleaning process.

Advanced Replace Options in Power Query

The "Replace Values" tool has more tricks up its sleeve. Click on Advanced options in the dialog box to see them.

• Match entire cell contents: This is incredibly useful. By default, Power Query will replace a text string even when it’s part of a larger word. For instance, if a cell contains "Manager - Sales Team" and you want to replace "Manager" with "Lead", the sentence will become "Lead - Sales Team". However, if you enable "Match entire cell contents," Power Query will only make the replacement if the entire cell contains just "Manager". It will leave "Manager - Sales Team" untouched.
• Match using special characters: This allows you to find and replace values like tabs (#(tab)) or line feeds (#(lf)) which are sometimes hidden in data exported from other systems.

Handling Case Sensitivity

By default, the standard "Replace Values" function in Power Query is not case-sensitive. This means if you ask it to replace a lowercase 'admin' with 'Administrator', it will also replace uppercase 'ADMIN' and mixed-case 'Admin'.

In most cases, this is helpful behavior. But if you need a case-sensitive replacement, you must use a different function, Text.Replace, within a custom column.

Here’s how to do it:

1. Navigate to the Add Column tab in the Power Query Editor and click Custom Column.
2. In the formula editor, give your new column a name (e.g., 'Position-Cleaned').
3. Write the following M code formula, replacing the placeholders with your own values:

Text.Replace([YourColumnName], "ValueToFind", "ReplacementValue", Comparer.Ordinal)

For example, to replace only the lowercase 'south' with 'South Region' in a column named [Territory], your formula would be:

Text.Replace([Territory], "south", "South Region", Comparer.Ordinal)

The key here is Comparer.Ordinal. It tells Power Query to perform an exact, case-sensitive text comparison. After creating the new column, you can delete the original column and rename the new one to match.

An Alternative Approach: Creating Calculated Columns with DAX

While Power Query is the best place to perform structural data cleaning, you can also use DAX (Data Analysis Expressions) to replace text within the report view itself. The difference is important:

• Power Query (M) changes the data permanently before it gets into your report model.
• DAX creates a new calculated column or measure that presents the data differently, but it doesn't change the underlying source data in the model.

Using DAX can be useful for report-specific changes or when you don't want to alter the base data. The most common function for this is SUBSTITUTE.

Using the SUBSTITUTE Function

The SUBSTITUTE function in DAX is case-sensitive and replaces existing text with new text in a string. Its syntax is:

SUBSTITUTE(<text>, <old_text>, <new_text>, [<instance_num>])

• <text>: The text or column you want to modify.
• <old_text>: The text string you want to search for.
• <new_text>: The text string you want to replace it with.
• [<instance_num>] (Optional): Which instance of the old_text to replace. Omitting this replaces all instances.

Example: Standardizing a URL prefix

Imagine you have a Product URL column where some links use an outdated prefix 'http://' while others use 'https://'. To create a new, consistent column:

1. Click on Data view (the table icon on the left).
2. Select the table containing your URLs.
3. Go to the Column Tools tab and click New Column.
4. Enter the following DAX formula into the formula bar:

Standard URL = SUBSTITUTE('Products'[URL], "http://", "https://")

This creates a new column named 'Standard URL', leaving your original URL column untouched but giving you a permanently cleaned version to use in your visuals.

Final Thoughts

Mastering find and replace actions is a massive step towards reliable and professional reporting in Power BI. Using Power Query’s built-in "Replace Values" tool is the best and most efficient way to clean your data at the source, while DAX functions like SUBSTITUTE offer flexibility for calculated columns directly within your models.

Ultimately, data cleaning is often the most time-consuming part of building reports. At Graphed, we aim to eliminate this completely. Instead of wrestling with Power Query or DAX cleanup steps, you can connect your scattered marketing and sales data sources in seconds. From there, you simply describe the dashboard or chart you want in plain English, and our AI-powered analyst builds it for you in real-time, handling the transformations behind the scenes.