Test for a Number is a very important topic in Power Query Suppose, you have a column of values and you need to test whether the value contained is a number or not.
Scenario 1 – When number contained is numeric
Then you can use following test condition to give True or False ([Data] is the column to be tested)
= Value.Is([Data],type number)
Scenario 2 – When number contained is text
If you use the above formula, then it will give False when the number is stored as text.
In this case, you can use the below
= try Number.From([Data]) is number otherwise false
But problem is that even logical true and false, dates and times will also be passing this test.
This limitation can be removed with this formula
=try if Text.Select(Text.From([Data]),{"0".."9",".","%"})=Text.From([Data]) then Number.From([Data]) is number else false otherwise false
This will work even for Scenario 1 as well.
Hence, my recommendation is to use the below one irrespective of the fact whether the number is stored as numeric or text.
=try if Text.Select(Text.From([Data]),{"0".."9",".","%"})=Text.From([Data]) then Number.From([Data]) is number else false otherwise false
I hope this tip on Test for a Number becomes useful to you. Power Query M function reference – PowerQuery M | Microsoft Docs