Number.NaN

A Constant Value in the Power Query M language that represents “Not-a-Number” (NaN). It is the result of dividing 0 by 0. The value is commonly used to indicate an indeterminate or undefined numeric result. Number.NaN is useful for error handling in invalid calculations, representing missing or unavailable data within a dataset, and identifying scenarios where the result cannot be represented as a valid number.

Examples

To access the constant value representing “Not-a-Number” (NaN) in Power Query, you can directly call the Number.NaN function without using any parentheses. This straightforward approach allows you to retrieve the symbol for NaN:

Number.NaN // Returns NaN

When performing operations that result in an indeterminate or undefined numeric result, such as dividing zero by zero, Number.NaN is the outcome:

0 / 0 // Returns NaN

However, you cannot directly compare the result to the string “NaN” to check if an operation returns NaN:

0 / 0 = "NaN" // Returns FALSE

There may however be cases where you want to check if your expression results in Not-a-Number (NaN). You would expect you can compare it to the constant like below.

0 / 0 = Number.NaN // Returns FALSE

Unfortunately that does not work. The Power Query M language does not directly support comparing the result to Number.NaN using the equality operator. Instead, you can use the Number.IsNaN function to test an expression for NaN and obtain the desired boolean result.

Number.IsNaN( 0 / 0 ) // Returns TRUE

Related constants

Other related constants are:

• Number.E
• Number.Epsilon
• Number.NegativeInfinity
• Number.PI
• Number.PositiveInfinity

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/number-nan