How to validate phone numbers?

[James__]

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

[V-yubandi-msft]

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.

[James__]

Thanks @Nasif_Azam 

I am using power BI desktop and SQL

[Nasif_Azam · Accepted solution]

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

[James__]

Thanks @Nasif_Azam  and @V-yubandi-msft  - this worked perfectly.

Thanks again.

James

[Nasif_Azam]

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