The M Code Behind the Power Query M function Number.IsNaN

What is Number.IsNaN Function?

The Number.IsNaN function in Power Query M language is used to check if a number is NaN. NaN is a value representing undefined or unrepresentable value in floating-point calculations. It is commonly used to represent invalid or missing data. The Number.IsNaN function returns true if the value is NaN, otherwise, it returns false.

The M Code Behind the Number.IsNaN Function

The M code behind the Number.IsNaN function is quite simple. Here is the M code for the Number.IsNaN function:

Number.IsNaN = (number) => number <> number;

The M code defines the Number.IsNaN function as a lambda function that takes a single argument, which is the number to be checked. The function then returns true if the number is not equal to itself, which is true only when the number is NaN.

How to Use Number.IsNaN Function in Power Query

Here is an example of how to use the Number.IsNaN function in Power Query:

Suppose we have a table with a column named "Amount" that contains some numeric values. We want to create a new column named "IsNaN" that indicates whether each value in the "Amount" column is NaN or not.

1. Select the "Amount" column in the Power Query Editor.

2. Go to the "Add Column" tab and select "Custom Column".

3. In the "Custom Column" dialog, enter "IsNaN" as the column name and enter the following formula:


4. Click "OK" to create the new column.

The formula uses the Number.IsNaN function to check whether each value in the "Amount" column is NaN or not. The function returns true if the value is NaN, false otherwise. The new "IsNaN" column will contain true or false for each value in the "Amount" column, indicating whether the value is NaN or not.

The Number.IsNaN function is a useful tool for checking whether a value is NaN or not in Power Query M language. The M code behind the function is simple and easy to understand. You can use the function in your Power Query projects to check for invalid or missing data and perform data transformations accordingly.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)