How to Replace Text in Power BI

Working with real-world data means dealing with messy, inconsistent text. One report might list a country as "USA," another as "United States," and a user-submitted form might have "U.S.A." or even a typo like "Unted States America." Getting these inconsistencies ironed out is essential for accurate reporting in Power BI. This guide will walk you through a few simple but effective methods to find and replace text, so you can clean your data and build reports you can trust.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Does Replacing Text in Power BI Matter?

Before jumping into the "how," it's helpful to understand the "why." Cleaning and standardizing text is not just about making your data look pretty, it directly impacts the quality of your analysis. Here’s when it’s most important:

• Data Standardization: This is the most common reason. You need "NY," "N.Y.", and "New York" to all be grouped as a single category, "New York." Without standardization, your charts and tables will show three separate entries, splitting your data and leading to incorrect conclusions.
• Correcting Errors: Typos and data entry mistakes happen. Replacing "Canda" with "Canada" or "Samsun" with "Samsung" ensures your filters and slicers work correctly and your reports are professional.
• Grouping and Re-categorization: Sometimes you need to simplify categories. You might want to replace various job titles like "Marketing Manager," "Content Lead," and "SEO Specialist" with a single broader category like "Marketing Team" for a high-level report.
• Removing Unwanted Characters: Your data might include unwanted characters, spaces, or formatting. You might need to remove dashes from product SKUs or strip currency symbols from a text column before converting it to a number.

Performing these changes in Power BI's Power Query Editor is the best practice. It means the transformations are applied to the source data every time you refresh, creating a repeatable and automated cleaning process. You set it once, and it works every time.

Method 1: The Quick and Easy “Replace Values” in Power Query

For simple find-and-replace tasks - like fixing a single typo or standardizing one term - the built-in "Replace Values" feature is your best friend. It’s fast, intuitive, and requires no code.

Let's say we have a 'Country' column with misspellings like "Canda" and we want to change it to "Canada."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Instructions:

1. Open Power Query Editor: In Power BI Desktop, click on the Transform data button on the Home ribbon. This will open the Power Query Editor, which is where you’ll perform most of your data cleaning steps.
2. Select the Column: Find and click on the header of the column containing the text you want to replace. In our example, we’d select the 'Country' column.
3. Choose Replace Values: With the column selected, go to the Transform tab in the ribbon and click Replace Values. (Pro Tip: You can also right-click on the column header and select "Replace Values" from the context menu).
4. Enter Your Values: A dialog box will appear.
5. Apply the Changes: Click OK. Power BI will instantly replace all instances of "Canda" with "Canada" in that column. You’ll see a new "Replaced Value" step added to the Applied Steps pane on the right. This keeps track of your changes and lets you undo them if needed.

You can repeat this process as many times as you need for different values within the same column.

Method 2: Using a Conditional Column for Smarter Replacements

Sometimes, your logic is more complex than a simple find-and-replace. You might need to replace a value based on a condition or consolidate multiple different values into a single new value. This is where a Conditional Column comes in handy.

Imagine your data has a 'Country' column with variations like "USA," "United States," and "America," and you want to standardize all of them to "US."

Step-by-Step Instructions:

1. Open Power Query Editor: If you're not already there, click Transform data.
2. Navigate to Add Column: Select the Add Column tab in the Power Query Editor ribbon. This allows you to create a new column without altering your original one, which is great for preserving the source data.
3. Choose Conditional Column: Click the Conditional Column button. This opens a user-friendly interface for building if-then-else logic.
4. Build Your Logic: Now, let's configure the rules in the dialog box.
5. Create the Column and Clean Up: Click OK. Power BI will generate your new "Country Standardized" column with the cleaned-up values. Now that you have a perfectly standardized column, you can remove the original 'Country' column to keep your data model tidy (right-click the header > Remove).

Method 3: Replacing Text with DAX Formulas

While Power Query is the preferred place for cleaning data, there are situations where you might need to replace text after the data is already loaded into your model. DAX (Data Analysis Expressions) is great for creating calculations and can also handle text manipulation in calculated columns or measures.

It’s important to understand the difference: Power Query changes happen before the data is loaded. DAX changes create a new column or calculation on the data that's already loaded.

Two primary DAX functions will help you here: SUBSTITUTE and REPLACE.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using the SUBSTITUTE Function

The SUBSTITUTE function is perfect for finding all instances of a specific text string within a column and replacing them with a different string.

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

Imagine you have product SKUs in a column named '[ProductSKU]' that look like "PROD-123-A." If you wanted to create a new column with the dashes removed, you could create a new calculated column with this formula:

SKU_Cleaned = SUBSTITUTE('Products'[ProductSKU], "-", "")

This formula scans the '[ProductSKU]' column, finds every dash "-", and replaces it with an empty string "", resulting in "PROD123A."

Using the REPLACE Function

The REPLACE function is a bit different. It targets text based on its position and length, not by matching a specific string. This is useful when you need to change a character at a known location.

Syntax: REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)

Let’s say you have a Transaction ID column named '[TransactionID]' where all IDs start with "INV-" (e.g., "INV-98765"). If you wanted to change the prefix to "SALE-" instead, you could use this calculated column formula:

TransactionID_New = REPLACE('Sales'[TransactionID], 1, 3, "SALE")

This tells Power BI to: go to the '[TransactionID]' column, start at the 1st character, replace the next 3 characters ("INV"), and insert the new text "SALE." The result would be "SALE-98765."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Final Thoughts

Knowing how to replace and standardize text is a fundamental skill for creating clean, reliable reports in Power BI. Whether you're using the simple 'Replace Values' feature in Power Query, building slightly more complex logic with a conditional column, or creating on-the-fly calculations with DAX, these methods will help you transform messy data into a powerful asset.

We know that endless data cleaning in tools like Power BI can be draining, especially when you have to repeatedly connect sources, apply cleaning steps, and update reports just to answer a simple question. We built Graphed to remove this friction entirely. Our platform connects directly to your marketing and sales tools and uses natural language to build the dashboard you need in seconds. Instead of figuring out complex transformations, you can just ask, "Show me a dashboard of sales by country," and get a live, cleanly-visualized report. We believe your time is better spent on strategy, not on manual reporting headaches.