Reply
Frequent Visitor
Posts: 2
Registered: ‎05-02-2018
Accepted Solution

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

_________________

1234567890

1111111111

123-456-7890

123-789-1230

XXXXXXXXXX

Null

 

I want to be able to create a condition in power bi to find the rows in my data that are not numeric.  When I load the data in power bi  the column is brought in as numeric and the rows that are not numeric result in error.  I would like to be able to display the rows in error so users have a dashboard to know which data rows they need to fix in the system the data is coming from.  I haven't been able to come up with a solution.  Any ideas?

 

 


Accepted Solutions
Highlighted
Advisor
Posts: 120
Registered: ‎11-10-2015

Re: Checking a column for alpha and alpha numeric characters

[ Edited ]

You can use the Value function together with IsError.

Like this:

 

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

 

Capture.PNG

 

 

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. 

 

Cheers, Edgar Walther
Power BI User Group Netherlands

View solution in original post


All Replies
Highlighted
Advisor
Posts: 120
Registered: ‎11-10-2015

Re: Checking a column for alpha and alpha numeric characters

[ Edited ]

You can use the Value function together with IsError.

Like this:

 

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

 

Capture.PNG

 

 

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. 

 

Cheers, Edgar Walther
Power BI User Group Netherlands
Frequent Visitor
Posts: 2
Registered: ‎05-02-2018

Re: Checking a column for alpha and alpha numeric characters

Thank you very much!