Is there an ISNUMBER() or ISTEXT() equivalent in Power Query

[Anonymous]

Hi Folks,

While data cleaning using the power query, I came across a situation where a column contains both text and numerical values. I want to add an additional column that states which one is number or text. In Excel, we have ISNUMBER() or ISTEXT().  Do we have any alternative in Power Query? Below is an Excel screenshot, an alternative of which want in Power Query.

[ryan_mayu · Accepted solution]

@Anonymous 

like what @CNENFRNL  suggested, you can try

if Value.Is([data],type number) 
then "Number"
else "Text"

 

[QooT]

Hi,
It is not working when you have column type text - then results is always Text,
It works when you first get valuefrom text, and then check type.

Value.Is(Value.FromText([data]), type number))

[MartinZima]

Thanks @QooT,

I was having this issue and even though my column data type was "Any", for whatever when PowerQuery parsed in the data everything became a string, so even values of 123 were resulting in Text, however your adjustment to the formula fixed this, so thanks!

IsNumber
--------
= if Value.Is(Value.FromText([--column-name-here--]), type number)
then "Y"
else "N"

[CNENFRNL]

Hi, @Anonymous , you may try Vaue.Is function,

https://docs.microsoft.com/en-us/powerquery-m/value-is