Forum Discussion

James__'s avatar
James__
Icon for Helper I rankHelper I
1 year ago
Solved

How to validate phone numbers?

Hi,   I have three different phone number fields in my data: PHONE1, PHONE2 and PHONE3 and they are listed for each customer. I am trying to identify the customers that don't have valid phone numb...
Need Help
  • Nasif_Azam's avatar
    Nasif_Azam
    1 year ago

    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

V-yubandi-msft's avatar
V-yubandi-msft
Icon for Community Support rankCommunity Support
1 year ago

Hi James__ ,

Thank you for reaching out to the Microsoft Fabric Community. In addition to Nasif_Azam , helpful response, I went ahead and tested your requirement   and I can confirm that the solution works perfectly.

Enter your customer data into Power BI.

2. Navigate to Home → Transform Data to access Power Query Editor.

3. In Power Query, create three new custom columns to verify the validity of each phone number.

 

let

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

in

    if Text.Length(CleanP1) = 11 and Text.StartsWith(CleanP1, "07") and CleanP1 <> "00000000000" and CleanP1 <> "12345678901"

    then "Valid" else "Invalid"

 

Repeat the same for PHONE2 and PHONE3 (rename appropriately). & Add a final column to classify the customer.

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

Load the data back and use visuals or filters to highlight only the Invalid Customers.

FYI:

 

 

I’ve also attached the .pbix file for your reference so you can review the working solution directly. Please check it out and let us know if you need any further adjustments.

 

 

Please consider marking this as a solution if it resolves your issue, as it will help others find it more easily too.