Replace NaN with 0 when creating custom column in Power BI

[lilySixteen]

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!

[harshnathani]

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!

[lilySixteen]

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

[amitchandak]

@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

[lilySixteen]

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

[amitchandak]

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

[CaesarRichards]

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.

[lilySixteen · Accepted solution]

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