Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Vijay_A_Verma

Test for a Number in Power Query M Language

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.

IsNumber.png

 

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.

ValueIs.png

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.

ValueIs1.png

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)

 

 

 

ValueIs2.pngBut 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) 

 

 

 

ValueIs3.png

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

 

 

 

ValueIs4.png

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