Forum Discussion

stfox's avatar
stfox
Helper I
10 years ago
Solved

ISNUMBER & ISTEXT

Hi Folks I need to search a column and determine if the contents contain just Text or  just Numbers . The dataset I am searching is largish (>5 M rows).  In Excel, the ISNUMBER  and ISTEXT functions...
Need Help
  • Sean's avatar
    Sean
    10 years ago

    stfox Yes in Excel cells can have their own data types while in PBI it's the whole column!

     

    However in both Excel and PBI you can tell the data type by just looking for this:

    TEXT - left justified

    NUMBER - right justified

    Which you can obvioualy confirm by looking at your pictures! The only strange thing is cell A2 in Excel?

    EDIT: I guess even specifically formatted as TEXT - the 55 (cell A2) is recognized in Excel as Number.

     

    Anyway what you can do is test if the value can be converted to a number...

     

    Can Convert = NOT ( ISERROR (Sheet1[Item to Test] + 0 ) )

     

     

    Hope this helps!

Sean's avatar
Sean
Community Champion
10 years ago

stfox Yes in Excel cells can have their own data types while in PBI it's the whole column!

 

However in both Excel and PBI you can tell the data type by just looking for this:

TEXT - left justified

NUMBER - right justified

Which you can obvioualy confirm by looking at your pictures! The only strange thing is cell A2 in Excel?

EDIT: I guess even specifically formatted as TEXT - the 55 (cell A2) is recognized in Excel as Number.

 

Anyway what you can do is test if the value can be converted to a number...

 

Can Convert = NOT ( ISERROR (Sheet1[Item to Test] + 0 ) )

 

 

Hope this helps!

  • stfox's avatar
    stfox
    Helper I
    10 years ago

    Thanks Sean 

    So essentially we check if an error is thrown when an addition (+0) is attempted on the contents of the column. 


    Cheers

    Steve