Forum Discussion
How to manage NaN or Infinity in PowerQuery (no Dax) ?
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).
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.
8 Replies
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.
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.
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]
I achieved the result by using
if (A / B) = Number.NegativeInfinity
then 0
else if (A / B) = Number.PositiveInfinity
then 0
else (A B)
Hi everyone,
One way to do this is with List.Max(your calculation, 0).CidcleyBarbosa, don't forget curly brackets!
List.Max( { yourCalculation, 0 } )
