Test for a Number in Power Query M Language

[Vijay_A_Verma]

Use Case - The question this time is how to test in Power Query M Language whether a given value is number or not. Below are a set of values and format they are stored in and what is the expected result when we test for a number.

Solution - Let's start building up the solution. We should first consider Value.Is function to achieve this. We will use following formula

= Value.Is([Number], type number)

We get following result.

As expected, it considers only those numbers as number which are already in number format but it won't consider numbers in text format. 

Now, we need to convert number stored as text into number format and use following formula

= Value.Is(Number.From([Number]), type number)

We get following result.

We get errors against dates and time stored in text format. Also date format and logical format are wrongly giving TRUE result. While error can be removed with try statement but this approach will still have issue in case of date format and logical format.

Now, let's use following formula to mitigate date format and logical format issue.

= Value.Is(Number.FromText([Number]), type number)

But we get many errors. Even 1.23E+11 which is in number format gives error.

Now, let's combine the approaches and use following formula

= Value.Is([Number], type number) or Value.Is(Number.FromText([Number]), type number) 

Still getting errors but 1.23E+11 which is in number format does not gives error. Now remaining errors are right errors which can be handled through a try statement.

Now, let's use following using try statement

= try Value.Is([Number], type number) or Value.Is(Number.FromText([Number]), type number) otherwise false

This gives right result. Hence, following formula can be used to test the number in a field in Power Query M Language

= try Value.Is([Number], type number) or Value.Is(Number.FromText([Number]), type number) otherwise false

As a bonus, here is an alternative formula which is shorter and preferred by me

= try Type.Is(Text.InferNumberType(Text.From([Number])), type number) otherwise false

 Below is the Excel file which has been used here - https://1drv.ms/x/s!Akd5y6ruJhvhuVo_xNAL11NuyOri?e=DSgRq6