There are a number of ways to find and replace values in Power BI. From conditionally replacing values based on a value in another column, to replacing values in multiple columns at the same time, handling null values, and replacing values in a single column of a dataset.
We’ll look at the importance of using replace values, and some additional considerations and best practice for how to replace values in Power BI
Table of Contents
Considerations for Replacing Values in Power BI
We recommend starting with the end user experience in mind when building Power BI reports and dashboards. The end user can be a report viewer, or it can also be a future developer that needs to update a Power BI dashboard when incoming data formats, or business changes require a report to be updated.
Because of the inevitable change, it’s a best practice to avoid replacing values in Power BI whenever possible. Instead, we recommend updating them in the source system such as the ERP or Accounting Software that the base data is coming from.
Values replaced in Power BI as part of a data prep step may be difficult for developers to identify or maintain if incoming data changes in the future.
However, we also recognize that updating source data is not always possible. There are also times that it may be too time consuming to coordinate with another department that manages the source system, or replacements are a one off.
How to Replace Values in Power BI
To replace values in Power BI, first launch the Power Query Editor. You can either right click on a data table from the data panel of the report view, and click Edit Query or click on Transform Data from the Home tab. After Power Query opens, right click on a column header and select Replace Values as shown in the picture below.
The Replace Values screen will appear. Type in the value to find and the value to replace in the text box prompts.
The Power BI replace screen also has advanced options to match entire cell contents. By default, every instance of a replacement target located in the selected column will be replaced with the new value. There is also an option to replace values with special characters that can be selected from a dropdown menu.
How to Replace Null Values in Power BI
Many businesses intelligence systems require a special syntax to replace null values in Power BI. Luckily, the Power Query editor is very straightforward in this manner. To replace null values, Right Click on a column header in the Power Query editor and select Replace Values.
Type “null” as the value to Find and the desired replacement value.
One of the most common scenarios is to replace a null value with a zero when you receive a calculation error. Null values are defined as having no value and can cause errors when attempting to aggregate them.
Find and Replace Values in Multiple Columns
To replace values in multiple columns at the same time, launch the Power Query Editor. Hold down SHIFT and select multiple columns. Next, Right Click on one of the selected columns and choose Replace Values. The Replace Values prompt will allow you to enter the value to find and the value to replace it with. Changes are applied to all selected columns in one step.
Note: You can multi-select columns in Power Query by holding shift and apply a number of different data preparation steps which includes but is not limited to replacing values.
Find and Replace All in Power BI
To find and replace all values in Power BI, launch the Power Query Editor. Then press CTRL + A to select All. All of the columns in the Power Query data model will be highlighted. Right click on one of the columns and select Replace Values. The change made for the value to find and replace will be applied to all selected columns.
Alternatively, you can hold down SHIFT and individually select each one of the columns in the data set, but using the keyboard shortcut to select all is faster when working with large numbers of columns.
How to Replace Values in Power BI Based on Values in Another Column
The easiest way to conditionally replace values in a Power BI column, based on the values in another column is a multi-step process. First, Launch the Power Query Editor. Next, create a new conditional column with all of the conditional criteria needed for replacements. Leave the ELSE value as the values in the original column. After creating a new column, delete the old one and re-name the new one to take its place.
We’ll break the steps down further in this example.
Step 1.) Launch the Power Query Editor
Step 2.) Create a Conditional Column from the Add Column tab of Power Query
The Add Conditional Column settings screen will appear. This screen allows you to dynamically update values based on the values of another column.
Click Add Clause to add multiple criteria to the conditional column.
Make sure that the Starting Column is listed as the Else Column.
The goal is to update all values with conditions, but return any existing values that are not being changed in the new conditional column.
Once you’ve added the conditions, press OK.
Step 3.) Delete the Original Column
To delete a column in Power Query, Right Click on the column name and select Remove
Note: This step is optional if you do not already have DAX formulas created in your report or dashboard. If you have DAX formulas in your report or dashboard they reference a table name and column name. By deleting the old column and renaming a new column to the same name, the change not be noticed by DAX because Power Query is executed prior to DAX formulas performing calculations.
Step 4.) Rename the New Conditional Column to Match the Old Column Name
Rename columns in Power BI by double clicking into the column name. This makes text selectable and allows you to write over it with a new name.
The end result of this process is a New Column that has the same name as an Old Column, but with updated values that have been conditionally replaced based on the values in another column. While there is an optional additional step of deleting and renaming columns, we find that this technique is generally easier than writing custom Power Query M code.
Replace Values Using Power Query M
When working with Power BI, we prefer to keep things low code or no code. It makes it easier for future users to make updates, and keeps things simple. However, we did want to mention that values can be replaced using Power Query M.
The example below uses Power Query to create a new custom column using the Text.Replace Formula
The syntax is as follows:
=Text.Replace([ColumnName],"Old Text","New Text")Here’s an example of the formula pointed at a Revenue Type Column, Finding Direct Sales and Replacing it with Retail Sales.
It’s also possible to update values conditionally in Power Query using the Advanced Editor. We also recommend avoiding the use of the Advanced Editor unless you are already familiar with Power Query. It is very easy to misplace a comma or modify the syntax of a query and cause an error which can be difficult to troubleshoot.
To learn more about the technique, the following video goes into great detail of the different steps involved.
As you can see there are a great many ways that you can find and replace values conditionally in Power BI, and Power Query M is a very powerful data transformation language.
Replace Values Using the DAX SUBSTITUE Formula
Power BI developers can replace specific values in a column using DAX formulas instead of Power Query. While we would recommend using Power Query for data transformations like these, it can be faster to do in DAX and DAX can be more appropriate for one off reporting situations where you need to change the value for a specific visual.
The syntax is as follows:
=SUBSTITUTE([ColumnName],"Old Text","New Text")Even with the ease of DAX, it’s better to update large volumes of data in Power Query. Power Query calculates changes during refreshes of a dataset, while DAX calculations occur when users adjust visuals, filters, and drill-down. Large numbers of substitutions can have a negative impact on user experience.
Conclusion
To find and replace values in Power BI, use the Power Query Editor. Null values can be replaced in Power BI by using the find value “null”.
You can right click on a column name to quickly find and replace values. Hold down Shift or press CTRL + A to select multiple or all columns at once then right click on a column title to replace values in multiple columns at once.
To dynamically replace values in Power BI based on conditions, create a new conditional column and use the existing column as the Else criteria after adding all other conditions. The result will be a new column that contains all replaced values and the original values. To avoid DAX formula errors, remove the old column and name the new column the same name.
Alternative options require more coding, such as writing Power Query M formulas or using the DAX SUBSTITUE formula.
Related Articles
- When to Use DAX vs Power Query in Power BI
- How to Extract Year, Month, or Day from a Date in Power BI
- Calculate the Difference Between Two Dates in Power BI
- Dynamically Refresh Power BI Datasets with Power Automate
- A Comprehensive Guide to Power BI AI Features
- How to Quickly Delimit, Parse, and Extract Text in Power BI
- Creating Financial Statements in Power BI