Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mr_Triongl
Frequent Visitor

How to validate phone numbers

Hi,

 

I have a list of phone numbers in my data and want to validate that the numbers are in the correct format - Starting with 01/+44 and 11 digits in total. Anything with less digits or greater to show as Invalid. Also any text in the phone number to be invalid as well

 

Below is what I'm trying to accomplish:

Asset_IDPhone NumberValidation
0101234567890Valid
02+441234567890Valid
03050547415Invalid
0401234567891 please call after 5pmInvalid
05051234567890Invalid
0601324567890Valid

 

1 ACCEPTED SOLUTION
adudani
Super User
Super User

hi @Mr_Triongl ,

 

create a new step ( press fx)

 

paste the following in the formula bar:

 

= Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Phone Number],"01",Comparer.OrdinalIgnoreCase) or Text.StartsWith([Phone Number],"+44",Comparer.OrdinalIgnoreCase) and Text.Length([Phone Number])= 11 then "Valid" else "Invalid")

adudani_0-1675817299832.png

 

Appreciate a thubs up if this is helpful.

 

Please accept as the solution if it resolves the question.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

View solution in original post

7 REPLIES 7
wdx223_Daniel
Super User
Super User

= Table.AddColumn(Source,"Validation",each let a={"01","+44"},b=List.PositionOf({"01","+44"},[Phone Number],0,(x,y)=>Text.StartsWith(y,x)) in if b>=0 and Text.Remove(Text.Range([Phone Number],Text.Length(a{b})),{"0".."9"})="" and Text.Length([Phone Number])-Text.Length(a{b})=9 then "Valid" else "Invalid")

adudani
Super User
Super User

hi @Mr_Triongl ,

 

create a new step ( press fx)

 

paste the following in the formula bar:

 

= Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Phone Number],"01",Comparer.OrdinalIgnoreCase) or Text.StartsWith([Phone Number],"+44",Comparer.OrdinalIgnoreCase) and Text.Length([Phone Number])= 11 then "Valid" else "Invalid")

adudani_0-1675817299832.png

 

Appreciate a thubs up if this is helpful.

 

Please accept as the solution if it resolves the question.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

I've noticed that this works for most telephone numbers, but it also sets numbers with 12 digits and any additional text as valid as well instead of setting them to invalid.

 

12 digit example - 012345678900  

Text in cell - 01234567890 please call after 5pm 

 

I'm trying to get these set to Invalid as well as they aren't correct because of the additional digit in one and that someone has added a note to the second one.

Thanks,

@Mr_Triongl  does splitting this column by delimeter ( space) take all notes to another column? 

If this is not the case will revamp the formula, could you please provide more sample inputs for those that are valid that should be invalid in a table? Thanks 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Thanks for the formula, when i ran it the following error appeared

Mr_Triongl_0-1675857100183.png

 

@Mr_Triongl 

Please replace #"Changed Type" to the name of your previous step.

The error will get resolved. 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Thanks for that @adudani that formula worked a treat.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors