How to Remove NaN in Power BI

Nothing's more frustrating than building what you think is the perfect Power BI report, only to see your visuals littered with the cryptic "NaN." This error, which stands for "Not a Number," can throw off calculations, create ugly blank spots in your charts, and make your entire dashboard look broken. This tutorial will walk you through exactly what NaN is and show you three straightforward methods to remove it using the Power Query Editor, from a quick fix to a more robust, long-term solution.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is "NaN" and Why Does it Appear?

Before fixing the problem, it helps to know what’s causing it. In Power BI and many other data tools, NaN is the specific result of a mathematical operation that is undefined. The most common cause is dividing zero by zero.

Imagine you're analyzing ad campaign data and want to calculate the Cost Per Click (CPC) with a formula like [Cost] / [Clicks]. If a particular campaign had zero cost and zero clicks for a given day, the calculation would be 0 / 0. Power BI doesn't know what to do with this, so it returns NaN.

It's important to distinguish NaN from null or blank values:

• Null/Blank: Represents the absence of a value. There's simply nothing there.
• NaN (Not a Number): Represents an actual, but invalid, numerical value that resulted from a calculation like 0/0.

Why bother fixing it? Because NaN values can wreak havoc on your reports. When a column containing NaN is used in a visual, it often appears as a blank space. Worse, aggregate calculations like SUM or AVERAGE might either ignore these rows or return an error, leading to misleading or incomplete insights. Cleaning them up is essential for accurate and professional-looking reports.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 1: The Quick and Easy "Replace Values" Fix

Let's start with the most direct method. If you just spotted a few NaN values in a column and want them gone fast, the "Replace Values" feature in Power Query is your best friend. This is the simplest approach and great for quick data cleaning sprints.

Step-by-Step Guide:

1. From your Power BI report, click on the "Transform data" button on the Home ribbon. This will open the Power Query Editor.
2. In the editor, find and select the column that contains the NaN values. The column header will turn green once selected.
3. Navigate to the "Transform" tab in the Power Query ribbon at the top of the screen.
4. In the "Any Column" group, click on "Replace Values."
5. A dialog box will pop up. In the "Value To Find" field, you need to type NaN. Casing does not matter, but the exact letters do. In the "Replace With" field, enter the value you want to use instead. Most often, this will be 0.
6. Click "OK."

Instantly, all the NaN values in your selected column will be replaced with 0. You can now click "Close & Apply" in the top-left corner to load your cleaned data back into your Power BI report.

What Should You Replace NaN With?

Replacing with 0 is the most common choice, and it's perfect if you're using the column for sums, counts, or other calculations where a zero makes sense. However, if you are performing an AVERAGE calculation, a zero can pull down your average. In that case, you might consider replacing NaN with null instead. Power BI's AVERAGE function will ignore null values, so your average will only be calculated from the rows that have legitimate data. To do this, simply type null into the "Replace With" box.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: Using a Conditional Column for More Control

Sometimes, a simple "find and replace" isn’t quite enough. You might want to replace NaN values based on some logic, or you might prefer to keep your original column intact while creating a new, cleaner version. For this, creating a Conditional Column is the ideal method.

This approach is less "destructive" because it creates a new column, leaving your original data untouched. It’s a great way to build a more organized and auditable data transformation process.

Step-by-Step Guide:

1. After opening the Power Query Editor, navigate to the "Add Column" tab in the ribbon.
2. Click on "Conditional Column." This will open a user-friendly rule builder.
3. Now, let's configure the rules to find and replace NaN:
4. Click "OK." This creates a new column but our logic isn't quite right. We need to tweak the M code Power Query just generated. Find the Formula Bar at the top (if you don't see it, go to the "View" tab and check "Formula Bar"). It will show something like: if [CPC] = 1 then 0 else [CPC]. We just need to change that [CPC] = 1 part.
5. Modify the formula to use M's specific checker for NaN values: Number.IsNaN(). The updated formula should look like this: if Number.IsNaN([CPC]) then 0 else [CPC]
6. Hit Enter. Your new column will now correctly show a 0 wherever a NaN existed in the original column, and the original value otherwise.

Once you are happy with your new column, you can right-click the header of the original dirty column and select "Remove" to keep your data model tidy.

Method 3: Preventing NaN in the First Place with M Code

The first two methods are reactive, they clean up NaN values after they've already been created. The most efficient and robust approach, however, is to be proactive and prevent them from ever appearing in the first place.

Since NaN is almost always caused by a division where the denominator is zero (specifically 0/0), you can adjust your calculation formula to gracefully handle this scenario. This fix tackles the problem at its source and is the method data professionals prefer.

Let's say you created your "CPC" column using the "Custom Column" feature with this simple formula:

=[Cost] / [Clicks]

We're going to add a simple if statement to check for a zero denominator before the division happens.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step-by-Step Guide:

1. In the Power Query Editor, find the "Applied Steps" pane on the right. Go back to the step where you originally created the custom column that's generating the NaN. You can do this by clicking the little cog icon next to the "Added Custom" step.
2. Instead of the simple formula, you'll enter a slightly more intelligent one that includes a check.

if [Clicks] = 0 then 0 else [Cost] / [Clicks]

1. Click "OK."

So what does this formula do? In plain English, it says:

"Check the value in the [Clicks] column. If it is 0, just output 0 as the result. Otherwise, if it's not 0, go ahead and perform the normal division of [Cost] / [Clicks]."

This simple check completely sidesteps the 0/0 error, preventing NaN values from ever contaminating your dataset. This makes your data model cleaner, more resilient, and easier for others to understand.

Final Thoughts

Cleaning NaN values is a crucial data preparation step that turns a messy, error-prone report into a clean and reliable one. Whether you choose the quick "Replace Values" option, the flexible Conditional Column method, or the robust "safe division" formula, you now have the skills to handle this common Power BI issue like a pro.

This kind of data janitor work - finding errors, cleaning columns, and wrestling with formulas - is exactly the type of tedious process we created Graphed to eliminate entirely. Instead of clicking through menus and editing M code, we allow marketers and sales teams to connect their data and create dashboards using plain English. You can simply ask, "Show me my CPC by campaign, and if clicks are zero, make sure the CPC is zero," and our AI will build a live, updating report that handles all that error logic for you, so you can spend your time on insights, not on fixing NaN warnings.