How to Replace Errors with Null in Power BI

Nothing stops a Power BI report in its tracks faster than a column full of "Error" messages. These errors can break your visuals, mess up your calculations, and undermine the credibility of your hard work. Fortunately, cleaning them up is straightforward. This tutorial will walk you through the best methods to replace errors with null values directly within Power BI, ensuring your reports are clean, accurate, and professional.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Bother Replacing Errors with Null?

You might be tempted to just filter out the rows with errors, but that's often a mistake. Ignoring errors means you’re potentially discarding valuable data. An error in one column might be on a row that contains crucial information in other columns - like sales data from a specific region or conversion details from a key marketing campaign.

Replacing errors with null values is the industry-standard best practice for a few key reasons:

• It preserves your data. The rest of the information on that row remains available for analysis.
• It keeps your calculations accurate. Power BI measures and calculations (both in Power Query and DAX) are designed to handle null or blank values gracefully. They’re typically ignored in aggregate functions like SUM, AVERAGE, or COUNT, which is exactly the behavior you want. Errors, on the other hand, can cause an entire calculation to fail.
• It results in cleaner visuals. A chart trying to plot an "Error" value won't render correctly, but it knows exactly what to do with a null - it simply skips the data point. Your line charts won't have strange breaks, and your bar charts will display correctly.

Essentially, by converting errors to null, you are telling Power BI, "I acknowledge there's no valid data here, so please ignore this specific value during calculations but keep the rest of the row." This simple step is fundamental to good data modeling and building reliable reports.

Your Data Cleaning HQ: The Power Query Editor

Before diving into the methods, it’s important to know where this data transformation happens. All of the recommended error handling should be done in the Power Query Editor. If you're new to Power BI, you can think of Power Query as your data preparation workshop. It's a separate window from the main reporting interface where you can connect to data, clean it, shape it, and get it ready before it's ever loaded into your report model.

To access it, click the "Transform data" button on the Home tab of Power BI Desktop. Any changes you make here are recorded as a series of steps that are automatically applied every time your data refreshes. Cleaning data in Power Query is ideal because you fix the problem at the source once, and it stays fixed.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 1: The Quick & Easy "Replace Errors" Feature

This is the most direct and common method for handling columns that already contain errors. It’s perfect for situations where errors pop up from issues like incorrect data types (e.g., text in a column of numbers) or problems from the source system.

Let's use a common marketing scenario. Imagine you've imported campaign data, but one column attempting to calculate Cost Per Click (CPC) has some errors due to a "divide by zero" issue for campaigns with no clicks.

Here are the step-by-step instructions to fix it.

Step 1: Select the Column(s)

Inside the Power Query Editor, find the column that contains the errors. You can select a single column by clicking its header. If you have multiple columns with errors you want to clean, you can hold down the Ctrl key and click on each column header to select them all at once.

Step 2: Find the "Replace Errors" Command

With the column (or columns) selected, right-click on one of the selected column headers. This will bring up a context menu with a long list of data transformation options. Near the bottom of this list, you'll find "Replace Errors."

Alternatively, you can find this command in the main ribbon. With the column selected, navigate to the "Transform" tab. In the "Any Column" group, you will see a "Replace Values" dropdown icon. Click the dropdown and select "Replace Errors."

Step 3: Enter "null" to Replace the Errors

A "Replace Errors" dialog box will appear. It asks what value you want to use to replace any errors it finds in the selected column(s). Here, you need to type the word null (without quotes, lowercase).

Power Query recognizes null as its specific signifier for an empty or non-existent value. Do not enter zero unless it is contextually appropriate for your data. In most cases, null is the correct choice because it represents an absence of a value, not a value of zero.

Click "OK."

Step 4: Verify the Result

Instantly, all the error cells in your selected column(s) will be replaced with null. You will see these cells displayed as empty or potentially with the word null in italics, depending on your version and settings. Power Query adds a new "Replaced Errors" step in the "Applied Steps" pane on the right-hand side. Your data's calculation column is now clean and ready for analysis.

Step 5: Click "Close & Apply"

Once you’re finished with all your data cleaning in Power Query, click the "Close & Apply" button in the top-left corner of the Home tab. This will close the Power Query Editor and load your cleaned data into the Power BI model, where you can start building visuals with confidence.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: Proactive Error Handling with a Custom Column

Sometimes, errors don’t come from your source data but are generated by a transformation you’re trying to perform within Power Query. A classic example is a division calculation (like A/B) where column B might contain zeros. While you could create a new column and then use the "Replace Errors" method on it, there is a more elegant and powerful solution: the try...otherwise expression.

This approach allows you to "try" an operation that might fail. If it succeeds, you get the result. If it fails (i.e., generates an error), you can specify what to do "otherwise" - in our case, return a null value. This is a robust way to handle potential errors before they even occur.

When to Use try...otherwise

• When you are creating a new column with a formula that could potentially produce errors (e.g., division, data type conversions).
• When you need more control over the logic compared to the standard "Replace Errors" feature.
• When you want to combine error handling with other logic in a single step.

Step 1: Add a Custom Column

In the Power Query Editor, go to the "Add Column" tab in the ribbon and click on "Custom Column."

Step 2: Write the try...otherwise Formula

The "Custom Column" dialog box will appear. Here, you'll enter your new column name and the M language formula. The basic syntax is:

try [Operation That Might Fail] otherwise [Value to Use if it Fails]

Let's use our CPC example again. Assume you have a [Total Cost] column and a [Total Clicks] column. A simple [Total Cost] / [Total Clicks] formula would fail if [Total Clicks] is zero.

Here’s how you’d write the safe version:

try [Total Cost] / [Total Clicks] otherwise null

Let's break down this formula:

• try: This tells Power Query to attempt the expression that follows.
• [Total Cost] / [Total Clicks]: This is the risky operation.
• otherwise null: If the try part fails for any reason (like a division-by-zero error), Power Query will skip the error and instead return null.

Another Common Example: Converting Text to Numbers

Imagine you have a column [Survey Rating] that is supposed to have numbers but sometimes contains text entries like "N/A". Trying to convert this column to a number type will create errors. You can use try...otherwise to handle this gracefully.

Your custom column formula would be:

try Number.From([Survey Rating]) otherwise null

This formula attempts to convert each value in the [Survey Rating] column into a number. If it succeeds, the number is returned. If it encounters a value like "N/A" and fails, it returns null.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Quick Note on DAX Error Handling

While Power Query is the best place to clean your data model, you can also manage errors during calculations in the report view using DAX (Data Analysis Expressions). This approach doesn't clean the source data, but rather handles errors on-the-fly inside your measures.

The two most common DAX functions for this are IFERROR and DIVIDE.

Using IFERROR

The IFERROR function evaluates an expression and returns a specified value if the expression returns an error, otherwise, it returns the value of the expression itself.

CPC Measure = 
IFERROR(
    SUM([Total Cost]) / SUM([Total Clicks]),
    BLANK()
)

In DAX, BLANK() is the equivalent of Power Query's null and is typically ignored by visuals.

Using DIVIDE

DAX has a built-in safe division function called DIVIDE that automatically handles division-by-zero scenarios.

CPC Measure = 
DIVIDE(
    SUM([Total Cost]),
    SUM([Total Clicks])
)

If the denominator is zero, DIVIDE will automatically return BLANK() by default, making your formula cleaner and easier to read.

So, when should you use Power Query versus DAX for error handling? Generally, always default to cleaning your data in Power Query. It results in a cleaner, more efficient data model and prevents errors from ever reaching your reports. Use DAX error functions primarily for handling errors within specific measures where the logic depends on user interactions or filters in the report itself.

Final Thoughts

Dealing with errors in Power BI doesn't have to be a headache. By using the "Replace Errors" feature or the try...otherwise function in Power Query, you can quickly clean up your data model, ensuring your calculations are sound and your visuals are accurate. Choosing the right method will help you build robust, reliable reports every time.

Handling tasks like these is a routine part of data analysis that often takes time away from finding actual insights. At Graphed , we automate the difficult parts of data analysis. Instead of manually cleaning errors or learning new query languages, you can connect your data sources in a few clicks and simply describe the reports and dashboards you want to see in plain English. Our AI-powered platform builds everything for you in real time, handling the data pipeline and cleanup behind the scenes so you can focus on making better decisions, faster.