Number.IsNaN
Tests whether a number is NaN, which stands for "not a number" and comes from an invalid calculation.
Number.IsNaN(number as nullable number) as logicalReturns: 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
- number: the value to test.
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
- Null is not NaN. Check for null with = null or the coalesce operator as well.
- A NaN can spread into totals and averages, so find it early.
- Most data should not contain NaN. When it does, the cause is usually a formula error in the source, so fix it there.
- The test returns a logical value, so it is easy to combine with and or not.
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.