How to Replace Values in Power BI

Cleaning up messy data is one of the most common tasks you'll face in Power BI, and knowing how to replace values is a fundamental skill for the job. Whether you need to correct typos, standardize categories, or bulk-update text, doing it efficiently will save you hours of manual work. This tutorial will walk you through exactly how to replace values in Power BI using the Power Query Editor, from simple find-and-replace actions to more advanced conditional logic.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Do You Need to Replace Values in Power BI?

Before jumping into the "how," let's quickly cover the "why." Your raw data is rarely perfect. It often comes with inconsistencies that can break your visuals and lead to inaccurate reports. Replacing values is your first line of defense for cleaning and preparing your data model.

Common scenarios for replacing values include:

• Fixing Misspellings and Typos: Correcting simple data entry errors like "Unted States" to "United States."
• Standardizing Categories: Ensuring consistency across your data, such as changing "USA," "U.S.," and "America" to a single standard, "United States." This is critical for getting accurate counts and groupings in your charts.
• Replacing Codes or Abbreviations: Swapping out abbreviations for full descriptions to make your reports more readable, like changing state code "NY" to "New York."
• Handling Null or Blank Values: Replacing empty cells with a specific value like "N/A," "Unknown," or zero to prevent errors in calculations and make your reports easier to understand.
• Grouping Data: Simplifying categories by replacing multiple distinct values with a single grouped value. For example, you might replace product names like "Espresso," "Latte," and "Cappuccino" with a broader category like "Coffee."

By handling these issues in Power Query, you create a repeatable, automated cleaning step. Every time you refresh your data, Power BI will automatically apply these replacements, ensuring your model stays clean and your final report is always accurate.

The Easiest Method: Using the "Replace Values" Feature

The most straightforward way to swap out data is with the built-in "Replace Values" function in the Power Query Editor. This is your go-to tool for quick, single-value replacements.

All data transformation in Power BI happens in the Power Query Editor. It's the engine room of Power BI where you shape and clean your data before it even hits your report canvas.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Open the Power Query Editor

First, open your Power BI Desktop file. In the Home tab of the main ribbon, click on the Transform data button. This will launch a new window: the Power Query Editor.

Step 2: Select the Column

In the Power Query Editor, you'll see a preview of your data tables. Find and click on the header of the column that contains the values you want to replace. The entire column will be highlighted to show it's selected.

For our example, let's say we have a 'Country' column with inconsistent entries like "U.S.A." that we want to standardize to "United States." We'll click on the 'Country' column header.

Step 3: Access the "Replace Values" Tool

You can find the "Replace Values" tool in two primary locations:

• In the Ribbon: With the column selected, navigate to the Transform tab in the ribbon. In the "Any Column" group, you'll see the Replace Values button.
• With a Right-Click: Alternatively, you can simply right-click anywhere on the selected column header. A context menu will appear, select Replace Values from the list.

Both methods will open the same "Replace Values" dialog box.

Step 4: Configure the Replacement

The dialog box is very intuitive. It has two main fields:

• Value To Find: Enter the exact text or number you want to find and replace. In our example, we would type "U.S.A." here. Keep in mind this is case-sensitive! "usa" will not match "U.S.A."
• Replace With: Enter the new value that will replace the old one. Here, we'll type "United States."

Once you've filled in both fields, click OK.

Power Query will scan the selected column, replace every instance of "U.S.A." with "United States," and add a new step called "Replaced Value" to the Applied Steps panel on the right. This lets you track - and undo - every transformation you make.

Going Deeper with Advanced Options

If you click the "Advanced options" dropdown in the "Replace Values" dialog, you’ll find a couple of useful settings to refine your replacement.

Match Entire Cell Contents

By default, "Replace Values" will replace a substring if it finds a match. For example, if you wanted to replace "Can" with "Canada" in a product column, it might incorrectly change "Soup Can" to "Soup Canada."

Ticking the Match entire cell contents checkbox prevents this. When enabled, it will only replace the value if the entire cell is an exact match for what you entered in "Value To Find." This gives you precision control and helps avoid unintended changes.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

How to Replace Multiple Values in a Single Column Efficiently

So, what if you have a list of values to replace? Say you need to change "U.S.A" to "United States," "U.K." to "United Kingdom," and "Ger" to "Germany."

You could repeat the "Replace Values" step for each one. This works perfectly fine, but it will create a separate "Replaced Value" step in your Applied Steps for each replacement. If you have dozens of values to change, this can make your query look cluttered and a bit harder to manage.

A slightly more advanced, but much cleaner, method involves using a bit of M code. You can daisy-chain multiple replacements into a single step using the Table.ReplaceValue function.

Here’s how you can do it:

1. Perform your first replacement using the standard user interface (e.g., replace "U.S.A." with "United States"). This will create the initial function for you.
2. In the Formula Bar at the top (if you don’t see it, go to the "View" tab and check "Formula Bar"), you will see the M code that was just generated. It will look something like:
3. Now, you can wrap this function inside another Table.ReplaceValue function to perform the next replacement. To replace "U.K" next, you would modify the formula to look like:

By nesting the functions like this, you can execute a long list of replacements within a single, organized step. It's a neat trick for keeping your query clean and efficient.

Using Conditional Logic to Replace Values

Sometimes, your replacement isn't a simple find-and-replace. You might need to replace a value in one column based on a condition in another column. The best tool for this job is the Conditional Column feature.

This feature doesn't technically "replace" values in the existing column, it creates a new column with the desired values. You can then remove the old column if you no longer need it.

Let's imagine you have a 'Product Sales' column and you want to create a new column called 'Sales Category' with values like "High" or "Low" based on the sales amount.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Open the Conditional Column Tool

In the Power Query Editor, go to the Add Column tab in the ribbon. In the "General" group, click on Conditional Column.

Step 2: Define Your Logic Rules

The "Conditional Column" window lets you build "if-then-else" statements without writing any code.

• New column name: Give your new column a clear name, like 'Sales Category.'
• Create your rule: Use the dropdown menus to build your logic. For our example, the rule would be:
• Set the "Else" value: If the condition is not met, you need a default value. In the Else field at the bottom, you can enter "Low."

You can add multiple rules by clicking the Add clause button to handle more complex logic (e.g., categories for "High," "Medium," and "Low" sales).

Step 3: Click OK

Click OK, and Power Query will generate your new 'Sales Category' column with the logic applied to every row. This is an incredibly powerful way to bucket, categorize, and clean your data based on business rules.

Final Thoughts

Perfectly clean data is the foundation of any reliable dashboard, and knowing how to use Power Query's Replace Values and Conditional Column features is essential for getting you there. By mastering these tools, you can automate your data preparation process, fix errors at their source, and save yourself countless hours of troubleshooting down the line.

We know that even with powerful tools like Power BI, getting your data connected and clean is often half the battle. At Graphed, we simplify this entire workflow. By connecting directly to your marketing and sales platforms like Google Analytics, Shopify, and Salesforce, we pull your data into one place automatically. Instead of clicking through menus to clean data, you can just ask in plain English to build the dashboards you need, while we handle the data wrangling in the background.