How to manage NaN or Infinity in PowerQuery (no Dax) ?

[LaurentZ]

Hi,

Is there an easy way to manage NaN or Infinity ?

I tried to replace error, no impact.

I tried to replace "NaN" by 0, no impact (when it's working with Null).

In my formula I tried with "try a/b otherwise 0" but I still have the error.

I saw some test based on Number.IsNaN but it just tests NaN... what about Infinity or other errors...

The only way I found is to test Numerator and denominator (if Numerator=0 or Denominator = 0 then 0 else .... ), but maybe there is a better methodology like an equivalent to IFERROR in Excel? 

Thank you for your help.

(I'm using PowerQuery in Excel, so I don't have DAX measures & operators).

[CidcleyBarbosa]

Hi everyone,
One way to do this is with List.Max(your calculation, 0).

[dufoq3]

@CidcleyBarbosa, don't forget curly brackets!

List.Max( { yourCalculation, 0 } )

[Diogo_Dalla]

I achieved the result by using

if (A / B) = Number.NegativeInfinity

then 0

else if (A  / B) = Number.PositiveInfinity

then 0

else (A  B)

[joponlu]

You can convert numbers to text and evaluate them as a string with NaN or ∞

if Number.ToText([number]) = "∞"

or Number.ToText([number]) = "NaN"

then 0 else [number]

[edhans]

You will need to use the Number.IsNaN, Number.PositiveInfinity, Number.NegativeInfiinity, etc. The try/otherwise construct often doesn't work with these because they are not errors - even though Excel would trap them with IFERROR().

I recommend this excellent article on this issue which goes through all of these and more functions, and includes a very helpful function at the bottom to check all of these at once.

[LaurentZ · Accepted solution]

Thank you @edhans for your answer.

So there is no native function in powerquery to valuate those kind of error... that's sad.

Thanks for the article, I'll give a try later.

By waiting I keep my methodology tant consits in checking if Numerator & denominator are not 0 before doing a division... not the smartest but at least it works.

[edhans]

Well @LaurentZ - it isn't technically an error, it is a valid result. But I do agree for most use cases NaN or any Infinity numbers aren't desired. The custom function I linked to will test for 4-5 results that are valid, but typically not desired, and remove them.

[Greg_Deckler]

@ImkeF @edhans