How to Remove Infinity in Power BI

Nothing stops a Power BI data refresh in its tracks quite like seeing a column suddenly filled with Infinity. It can turn a clean-looking table into a confusing mess and cause your visualizations to break entirely. This article will show you exactly why this happens and give you several tried-and-true methods to remove those infinite values using the Power Query Editor, ensuring your reports are clean, accurate, and professional.

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 Does Power BI Show 'Infinity'?

The infamous Infinity value in Power BI is almost always the result of a single mathematical operation: division by zero.

In standard mathematics, dividing a number by zero is considered "undefined." Different tools handle this in different ways. Some might throw an error and stop the process, while others return a NULL value. Power BI's Power Query engine, however, returns Infinity when a positive number is divided by zero, and -Infinity when a negative number is divided by zero.

Let's look at a common scenario in marketing analytics. Imagine you're calculating an important metric like Ad Spend per Website Session in a custom column:

[Ad Spend] / [Sessions]

This works perfectly for days when you had sessions. But what if you had a day where you spent $50 on an ad campaign but, for some reason, it generated zero sessions? Power BI will calculate:

50 / 0

And the result in your table will be Infinity. This is a problem because:

• It breaks visuals. How can you draw a bar on a chart that is infinitely high? You can't, so the visual will often fail to render or show an error.
• It distorts aggregations. Trying to calculate the average, sum, or any other aggregation on a column containing an Infinity value will lead to incorrect or nonsensical results.
• It's unprofessional. Sharing a report dotted with Infinity tells your stakeholders that the data isn't clean or reliable.

Fortunately, fixing this is straightforward once you know where to look. We'll handle this in the Power Query Editor, which is the best place to clean and transform your data before it gets loaded into your report model.

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

Method 1: The Easiest Fix using 'Replace Values'

This is the quickest and most direct way to eliminate infinity values after they have already been generated. It's perfect if you just need a simple fix and don't want to mess with formulas.

Let’s say you’ve already created your Ad Spend per Session column and it’s full of Infinity values. Here's how to fix it.

Step 1: Open Power Query Editor

From the main Power BI Desktop window, go to the Home tab and click on Transform data. This will launch the Power Query Editor where you can transform your raw data.

Step 2: Select the Problematic Column

Find and click on the header of the column that contains the Infinity values. This will select the entire column.

Step 3: Use the Replace Values Feature

With the column selected, navigate to the Transform tab in the ribbon at the top of the editor. Find the Replace Values button and click it.

Step 4: Configure the Replacement

A new window will pop up:

• In the Value To Find box, type Infinity. Power Query is case-sensitive, so make sure to use a capital "I".
• In the Replace With box, enter the value you want to see instead. Usually, 0 or null is the best choice.

Choosing Between 0 and Null: Your choice here matters. If you replace Infinity with 0, those rows will be included in calculations like an average, potentially lowering it. If you use null, those rows are often ignored in calculations, which might represent the situation more accurately. For most use cases, 0 is a safe and understandable default.

Click OK. You'll see all instances of Infinity disappear from your column, replaced by the value you specified. If you also have -Infinity values, you'll need to repeat the process to replace those as well.

Method 2: A More Robust Fix with a Custom Column

Replacing values works, but it’s a bit like cleaning up a mess after the fact. A more robust approach is to prevent the division-by-zero error from happening in the first place. You can do this by adding a conditional custom column.

This method doesn’t just fix the problem, it also makes your logic clearer to anyone who might edit the report later.

Step 1: Open Power Query Editor and Locate Your Data

Again, click Transform data from the Home ribbon. Find the table where your source columns (e.g., Ad Spend and Sessions) are located.

Step 2: Add a Custom Column

Navigate to the Add Column tab in the Power Query ribbon and click on Custom Column.

Step 3: Write a Conditional Formula

The Custom Column window is where you'll write a simple M formula. Let's create a new, clean SpendPerSession column:

• In New column name, type SpendPerSession.
• In the Custom column formula box, enter the following logic:

if [Sessions] = 0 then 0 else [Ad Spend] / [Sessions]

Let's break this down:

• if [Sessions] = 0: This is the check. It looks at each row to see if the value in the Sessions column is zero.
• then 0: If the check is true (meaning sessions are zero), it returns 0 for this row instead of attempting the division.
• else [Ad Spend] / [Sessions]: If the check is false (sessions are not zero), it goes ahead and performs the safe calculation.

Click OK. Power BI will create your new column, now completely free of any Infinity values. You can then remove your old, problematic column to keep your model tidy. This approach is cleaner and more reliable because it addresses the root cause of the problem.

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

Method 3: Editing the M Code Directly for Maximum Efficiency

For those feeling a bit more adventurous, you can directly edit the M language code generated by Power Query. This can be the most efficient solution, especially if you want to fix an existing custom column without creating a new one.

A more powerful way to handle potential errors in Power Query is with a try...otherwise expression. It’s cleaner than an if statement because it will catch any error from the division, not just division by zero.

Let's say you've already added a custom column with the simple formula [Ad spend] / [Sessions] and it's generating Infinity values.

Step 1: Select the Step that Created the Column

In the Query Settings pane on the right of the Power Query Editor, look at your list of Applied Steps. Click on the step where you created the initial calculation (it will probably be named "Added Custom").

Step 2: Modify the Formula in the Formula Bar

With the step selected, look at the formula bar just above your data table. You'll see the original M code. We will modify it to include the try...otherwise logic.

Your original formula might look like this:

Table.AddColumn(#"Previous Step Name", "SpendPerSession", each [Ad Spend] / [Sessions])

Modify the formula to read:

Table.AddColumn(#"Previous Step Name", "SpendPerSession", each try [Ad Spend] / [Sessions] otherwise 0)

The try keyword tells Power Query to attempt the calculation [Ad Spend] / [Sessions]. If that calculation is successful, it returns the result. If it fails for any reason (like dividing by zero), the otherwise 0 statement kicks in and returns 0 instead of an error or Infinity.

Press Enter, and you have instantly fixed the column in place without any extra steps or columns.

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

What About Handling Infinity in DAX?

It’s important to distinguish between cleaning data in Power Query and performing calculations in DAX. The general rule of thumb is to handle data quality issues - like invalid numbers or errors - as far upstream as possible. This means Power Query is the preferred place to remove Infinity.

However, if for some reason you must handle division by zero within a DAX measure, there’s a proper way to do it. Instead of using the slash (/) for division, use the DIVIDE() function.

In a DAX measure, this would be unsafe and could produce infinity:

Unsafe Spend Per Session = SUM('YourTable'[Ad Spend]) / SUM('YourTable'[Sessions])

The correct, safe DAX formula is:

Safe Spend Per Session = DIVIDE( SUM('YourTable'[Ad Spend]), SUM('YourTable'[Sessions]), 0 )

The DIVIDE() function intelligently handles division by zero. The first two parts are the numerator and denominator. The optional third part (in this case, 0) is the alternate result to return if the denominator is zero. This syntax is clean, clear, and error-proof.

Final Thoughts

Dealing with Infinity values in Power BI is a common rite of passage for analysts. Now you know it’s simply a result of dividing by zero and can be easily fixed within Power Query using Replace Values, a conditional custom column, or by editing the M code directly. Cleaning this up at the source will keep your data model solid and your visuals reliable.

We know how annoying it is when technical details like this break your workflow and steal time that could be spent on actual analysis. That’s why we created Graphed. It’s designed to sidestep many of these manual data cleaning and report-building hurdles. After connecting your Shopify, Google Analytics, or other marketing and sales platforms, you can simply ask for the dashboard you need in plain English. Our AI handles the transformations in the background to build live, accurate reports, letting you focus on answering business questions rather than troubleshooting formula errors.