How to validate phone numbers?

Hey James__ ,

Since you're using Power BI Desktop and SQL, here are solutions for both:

Power BI (Power Query): You can clean and validate phone numbers using Power Query steps:

1. Remove non-numeric characters:

   Text.Select([PHONE1], {"0".."9"})

2. Create a custom column to validate:

   let
       CleanPhone = Text.Select([PHONE1], {"0".."9"})
   in
       if Text.Length(CleanPhone) = 11 and Text.StartsWith(CleanPhone, "07") and CleanPhone <> "00000000000" then "Valid" else "Invalid"

3. Repeat for PHONE2 and PHONE3, then create a final column like:

   if [PHONE1_Valid] = "Valid" or [PHONE2_Valid] = "Valid" or [PHONE3_Valid] = "Valid" then "Valid Customer" else "Invalid Customer"

SQL Approach:

SELECT *,
  CASE 
    WHEN 
      (LEN(PHONE1) = 11 AND PHONE1 LIKE '07%' AND PHONE1 NOT IN ('00000000000', '12345678901')) OR
      (LEN(PHONE2) = 11 AND PHONE2 LIKE '07%' AND PHONE2 NOT IN ('00000000000', '12345678901')) OR
      (LEN(PHONE3) = 11 AND PHONE3 LIKE '07%' AND PHONE3 NOT IN ('00000000000', '12345678901'))
    THEN 'Valid'
    ELSE 'Invalid'
  END AS PhoneNumberStatus
FROM YourTable;

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam