Reply
lilySixteen
Helper III
Helper III
Partially syndicated - Outbound

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!

 

1.png

1 ACCEPTED SOLUTION

Syndicated - Outbound

@amitchandak Yes. please see the error message below when use "DIVIDE" in the custom column. 

1.png

2.png

 

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!

 

View solution in original post

7 REPLIES 7
harshnathani
Community Champion
Community Champion

Syndicated - Outbound

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!

 

Syndicated - Outbound

@harshnathani Thanks! This has already been resolved before you post your answer. I think your answer would work correctly as well. Thanks though!

amitchandak
Super User
Super User

Syndicated - Outbound

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

@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.

Syndicated - Outbound

@lilySixteen , I did not get can not use divide?

Screenshot 2020-07-14 23.08.45.png

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

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.

Syndicated - Outbound

@amitchandak Yes. please see the error message below when use "DIVIDE" in the custom column. 

1.png

2.png

 

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!

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)