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
Impactful 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. 

 

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