Downloads
Power Query MNumberAdvanced

Number.IsNaN

Tests whether a number is NaN, which stands for "not a number" and comes from an invalid calculation.

Syntax
Number.IsNaN(number as nullable number) as logical

Returns: true or false.

What it does

NaN is a special value that appears when a calculation has no valid number as its result. It is different from null, which means no value at all. Number.IsNaN tests for it, so a bad ratio that came through a spreadsheet import can be found and removed. A null is not NaN, so the two checks are separate.

Syntax and arguments

Example

A ratio column came from a spreadsheet where some divisions had failed. The team keeps only the rows where the ratio is a real number before the average is taken.

let
    Ratios = #table(type table [Vendor = text, Ratio = number],
        {{"Acme Supply", 0.92}, {"Blue Harbor", 1.05}, {"Northwind", 0.88}}),
    Valid = Table.SelectRows(Ratios, each not Number.IsNaN([Ratio]) and [Ratio] <> null),
    Average = List.Average(Valid[Ratio])
in
    Average

All three rows are valid here, so the average is taken over all of them. On a real import, rows that failed in the spreadsheet would be removed before the average, so one bad division does not affect the figure.

Example: a second, different use

Check a column of ratios after a load and count the bad rows, so the team can fix the source before the report goes out.

Things to watch

In business terms

Ratios such as margin, days sales outstanding and utilization are calculated in spreadsheets that sometimes divide by zero. Filtering out the invalid results before an average is a simple data quality step that prevents a misleading report.

Related functions

All Power Query M functions · View as Markdown · M basics