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 work at the cell level and produce what I am after (see screenshot below). DAX calculations in PowerBI appear to work differently- they appear to be working on the datatype of the column (also in screenshot).   I can't see a simple/ elegant  PowerQuery solution.  I am pulling data from a SQL Server DB, so contemplating  pulling the data from a SQL View that contains Regex that does the search, or perhaps just taking the "WHERE  ...."LIKE" route with wildcards.

 

 In terms of processing power- I have a Virtcual Machine with PowerBI running with  RAM 8 (althjough I could dial this up to 16 GB), and the SQL DB has 64 GB.

 

Can anyone suggest the best approach/ pros vs cons, considering the dataset size?

Cheers

Steve 

 

  Here is a link to my PowerBI file

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!

2 Replies

  • 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