Forum Discussion

LaurentZ's avatar
LaurentZ
Icon for Helper I rankHelper I
6 years ago
Solved

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 "tr...
Tips and Tricks
edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    LaurentZ
    Icon for Helper I rankHelper I
    6 years ago

    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's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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.