How to check for numbers in Power Query and Power BI (IsNumber)

In this article, we show you how to check for numbers in Power Query and Power BI, as well as how to use the IsNumber DAX function.

Power Query does not have an IsNumber function, but we have a way to mimic it.

Power BI does have an IsNumber function, but it is not always practical. We share two alternative methods you can use to identify numeric values in your dataset.

How to check for numeric values in Power Query

In Power Query, we can check for numbers in a column by mimicking the IsNumber function.

Here’s an example of a column with some numeric and text values.

We want to identify which rows contain numbers.

First, we create a custom column in Power Query.

To mimic the IsNumber function and find the numbers, we need to use the Value.Is function as well as the Number.From function.

This will check the values in your column and return an error if they are not numeric.

We have wrapped the formula in an ‘IF’ statement and ‘try otherwise’ to return ‘Number’ or ‘Not Number’.

Here’s the formula we used in the custom column:

= try if Value.Is(Number.From([Column]), type number) then "Number" else "Not Number" otherwise "Not Number"

Here is the result in Power Query:

How to check for numeric values in Power BI

In Power BI, you cannot always use the IsNumber DAX function to check to see whether values in a column are numbers.

The IsNumber function does not work when the column you are using is type ‘text’. If your column contains a mix of numbers, text or dates, then it will normally be type text.

However, we can mimic the IsNumber function in Power BI to check for numeric values in a column.

Here’s some example data which is a mix of text values and numbers:

If we use the IsNumber DAX formula in a custom column, because it is a ‘text’ column, it returns ‘False’ for all of the rows, even for the numeric values.

Since we can’t use IsNumber in this case, we have to use alternative DAX functions.

One way we could test for numbers in this column would be to use the VALUE, which converts numbers in a text column to numbers a number format.

We wrap this in an ISERROR function so that values that are not numbers do not return errors. Instead, rows with a number in will return ‘True’ and rows with values that are not numeric will return ‘False’.

Here is the formula we would use in our custom column:

= ISERROR (VALUE(Table[Column]))

Here is the result in Power BI:

Another way of checking to see if values in a column are numeric is to perform a calculation on them which can only be done if the value is a number.

In this case, we have used the MOD DAX function, which performs a numeric calculation. If an error occurs (because the value in the column we are testing is not a number) it will return an error.

We have wrapped this within an IF statement to return either “Number” or “Not Number”.

Here is the formula we would use in our custom column:

= IF(ISERROR((MOD(Table[Column], 2 ))), "Not Number", "Number" )

Here is the result in Power BI:

These two methods are great for differentiating numeric and text values in a column.

However, if your column also has dates in it, these will not work.

This is because dates are converted to numeric values and can have calculations performed on them.

If we use the previous example but add a date value to our original column, Power BI is able to perform a calculation using the date’s numeric value and therefore returns ‘Number’:

To solve this, we have to test for date values in our column first and then for numeric values second.

We have covered how to do this in a separate article which is linked above.

Our custom column formula will now test for date values first using DATEVALUE, and then if the row value is not a date, it will then test for numeric values.

In this example, we have used the formula from the second example to test for numeric values.

Here is the formula we would use in our custom column:

= IF(ISERROR(DATEVALUE(Table[Column])),(IF(ISERROR((MOD(Table[Column], 2 ))), "Not Number", "Number" )),"Not Number")

Here is the correct result in Power BI:

How to use IsNumber in Power BI

IsNumber is a DAX function that can be used in Power BI to check whether values are numbers.

Although we have discussed its limitations in the previous section, it can be useful to use in conjunction with other functions, such as the example below.

Here is some example data with numeric values but also nulls:

When we use the IsNumber function in Power BI on this column, it produces the following result:

= ISNUMBER(Table[Column])

The column returns either ‘True’ or ‘False’ depending on if the row contains a number or not.

We can pair this with other functions, such as IF to manipulate our data as desired.

In this example, values of 80 or above are not entered.

Therefore, we can categorize these into ‘Under 80′ and ’80+’ using IF and IsNumber.

= IF(ISNUMBER(Data1[Age])=True,"Under 80","80+")