Forum Discussion

whitakerj's avatar
whitakerj
Regular Visitor
8 years ago
Solved

Checking a column for alpha and alpha numeric characters

Hello,   I am relatively new to power bi so please be patient.    I am trying to audit data and flag rows that are not all numeric.     Here is a sample of my data    Phone Number __________...
  • waltheed's avatar
    waltheed
    8 years ago

    You can use the Value function together with IsError.

    Like this:

     

    IsNum = NOT(ISERROR(VALUE([Phone])))

     

     

     

    If you want to alow dahes between the numbers you can add a SUBSTITUTE function around the [Phone] field.

     

    IsNum = NOT(ISERROR(VALUE(SUBSTITUTE([Phone];"-";""))))

    Hope this helps. 

     

waltheed's avatar
waltheed
Icon for Impactful Individual rankImpactful Individual
8 years ago

You can use the Value function together with IsError.

Like this:

 

IsNum = NOT(ISERROR(VALUE([Phone])))

 

 

 

If you want to alow dahes between the numbers you can add a SUBSTITUTE function around the [Phone] field.

 

IsNum = NOT(ISERROR(VALUE(SUBSTITUTE([Phone];"-";""))))

Hope this helps. 

 

  • whitakerj's avatar
    whitakerj
    Regular Visitor
    8 years ago

    Thank you very much!

  • Romain_FOURNIER's avatar
    Romain_FOURNIER
    Frequent Visitor
    6 years ago

    Hello,

    Thank for the solution :

    IsNum = NOT(ISERROR(VALUE([Phone])))

    It seems to work most of the time :
    NOT(ISERROR(value([ColumnWithLetters]))) gives FALSE.

    NOT(ISERROR(value([ColumnWithLeadingZeros]))) gives TRUE.
    NOT(ISERROR(value([ColumnWithNULLValue]))) gives FALSE.

     

    Unfortunately, it does not work for empty fields (which are not numbers) : 
    NOT(ISERROR(value([ColumnWithEmptyValue]))) gives TRUE

    NB : NOT(ISERROR(value(""))) works : it gives FALSE.

     

    Here is an alternative partial solution (digits from 0 to 9 are encoded from 48 to 57, See https://en.wikipedia.org/wiki/List_of_Unicode_characters#Basic_Latin) : 

    IsNum = UNICODE([Phone])>=48) && UNICODE([Phone])<=57

    Howerver, this formula tests only the first character of the string : 

    https://docs.microsoft.com/en-us/dax/unicode-function-dax