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 numbers due to the fields being blank or filled with nonesense like "000000" etc.

 

In some cases there are spurious phone numbers in PHONE1, but genuine phone numbers in PHONE2 and PHONE3 is blank etc.

 

I live in the UK and phone numbers are generally 11 digits long.

 

Can anyone help, please?

 

Many thanks

 

 

 

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

5 Replies

  • Nasif_Azam's avatar
    Nasif_Azam
    Icon for Super User rankSuper User
    1 year ago

    Hey James__ ,

    Since you're working with UK-based phone numbers, a common format to expect is exactly 11 digits, typically starting with 07 for mobile numbers. To identify customers with invalid phone numbers across PHONE1, PHONE2, and PHONE3, you can follow these steps:

     

    1. Clean the Data: Remove any spaces, dashes, or non-numeric characters from each phone number field.

    2. Check for Proper Length: Only keep numbers that are exactly 11 digits long.

    3. Filter Out Obvious Invalid Entries: Watch for patterns like "00000000000", "12345678901", or repeated characters.

    4. Validate Format (Optional): If you're checking mobile numbers specifically, you can use a pattern like 07XXXXXXXXX (where X = digit).

     

    If you're using tools like Excel, Power BI, SQL, or Python, let me know I’d be happy to help with a more specific approach. Also, when evaluating a customer, consider them valid if at least one of the three phone fields contains a genuine number.

     

    For Detailed Information:

    How to Clean and Validate Phone Numbers in Excel – ExcelJet

    Validate Phone Numbers in Power BI using Power Query – RADACAD

    SQL Query to Validate Phone Numbers – Stack Overflow Thread

     

    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

    • Nasif_Azam's avatar
      Nasif_Azam
      Icon for Super User rankSuper User
      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.