Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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.

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    Anonymous 

    like what CNENFRNL  suggested, you can try

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

     

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    like what CNENFRNL  suggested, you can try

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

     

    • QooT's avatar
      QooT
      Frequent Visitor
      3 years ago

      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's avatar
        MartinZima
        New Member
        3 years ago

        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"