- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Replace NaN with 0 when creating custom column in Power BI
Hello,
When divided by zero, it generates NaN and infinite value. Can you please help me with how to replace these values with zero when creating custom columns? The picture below, "Collection in %_June" is a custom column, the current formula is [Collections_June]/[Billings_June] which produces NaN and infinite values when column Billings_June contains zero.
Thanks so much!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@amitchandak Yes. please see the error message below when use "DIVIDE" in the custom column.
I just figured out this can be resolved by "if...then...else...". Here is the solution and it worked -
if [Billings_June]=0
then 0
else [Collections_June]/[Billings_June]
Thanks though!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @lilySixteen ,
I assume you are showing these details of Power Query and it is showing NAN or Infinity as [Billings_June] is 0 in some rows.
SO when you create a Custom Column
Write this
if [Billings_June] <> 0 then [Collections_June]/[Billings_June] else [Collections_June]
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@harshnathani Thanks! This has already been resolved before you post your answer. I think your answer would work correctly as well. Thanks though!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@lilySixteen , I can formula is created in power bi, then create like
divide([Collections_June],[Billings_June])
If created elsewhere. Use replace function in power query
https://yodalearning.com/tutorials/learn-how-replace-values-power-query/
You should able search using NaN
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@amitchandak Can't use "divide" in the Custom column. The replace value option doesn't work well, after replace "NaN" with "0", there's still NaN values showing in the column.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This happens because you are using DAX and the question is for M, in PowerQuery. I had the same problem and found this answer by coincidence, it doesn't really solve the problem.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@amitchandak Yes. please see the error message below when use "DIVIDE" in the custom column.
I just figured out this can be resolved by "if...then...else...". Here is the solution and it worked -
if [Billings_June]=0
then 0
else [Collections_June]/[Billings_June]
Thanks though!

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
06-05-2024 02:40 AM | |||
06-06-2024 05:29 AM | |||
02-15-2024 06:03 AM | |||
07-26-2023 12:31 PM | |||
04-10-2024 11:19 AM |
User | Count |
---|---|
90 | |
82 | |
57 | |
41 | |
39 |