Number.NaN is 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.
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
Other related constants are: