Ravindra BagaleCourses & study guides मराठी Track your progress

7. Data Cleaning A–Z in Power Query

7.13 Phone Numbers, E-mails and PIN Codes

By Last updated:

Start with a problem

The customer list has phones in four formats, one email without a dot in the domain, and a 4-digit PIN. The SMS team needs clean 10-digit numbers and valid flags.

Explain simply

Standardise first (keep digits, trim, lowercase), then validate with simple rules and add OK flags instead of deleting rows.

Show the data

Before:

Customer ID Phone Email PIN
C01 +91 90000 00001 ravindra@example.com 411038
C02 9000000002 "SHRADDHA@EXAMPLE.COM " 422013
C03 090000 00003 ruhi@example 416008
C04 90000-00004 salman@example.com 4220

About this data: This table uses columns: Customer ID, Phone, Email, PIN. This table uses columns: Check, OK, Not OK. This table uses columns: Customer, Phone, E-mail, Pincode. This table uses columns: Customer, Phone, Phone OK, E-mail, E-mail OK, Pincode. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_customers_text.csv (source: src/practice/).

Demonstrate

  1. Add Custom Column Phone10 = Text.End(Text.Select([Phone], {"0".."9"}), 10).

    Your result should look like this:

    Phone10
    9000000001
    9000000002
    9000000003
    9000000004

  2. Email › Format › Trim, then lowercase.

    Your result should look like this:

    C02 Email
    shraddha@example.com

  3. Custom Column Email OK = Text.Contains([Email], "@") and Text.Contains(Text.AfterDelimiter([Email], "@"), ".").

    Your result should look like this:

    Email OK
    TRUE, TRUE, FALSE (C03), TRUE

  4. Custom Column PIN OK = Text.Length([PIN]) = 6.

    Your result should look like this:

    PIN OK
    TRUE, TRUE, TRUE, FALSE (C04)

Show the result

Your result should look like this (fictional sample):

Check OK Not OK
Phone (10 digits) 4 0
Email 3 1 (C03)
PIN 3 1 (C04)

Explain what happened

Text.Select keeps digits only (removing +, spaces, hyphens); Text.End keeps the last 10, dropping 91 or a leading 0.

Explore further

  • Keep PIN and phone as Text so leading zeros are not lost.

Detailed reference for this lesson:

Before

Customer Phone E-mail Pincode
Ravina +91 90000 00001 Ravina@Example.com· 411038
Raja 090000-00002 raja.example.com 431 003
Salman 91-9000000003 SALMAN@EXAMPLE.COM 416008.0
Rani 12345 rani@example 440010

After

Customer Phone Phone OK E-mail E-mail OK Pincode
Ravina 9000000001 TRUE ravina@example.com TRUE 411038
Raja 9000000002 TRUE raja.example.com FALSE 431003
Salman 9000000003 TRUE salman@example.com TRUE 416008
Rani 12345 FALSE rani@example FALSE 440010

(Phone numbers and e-mails are fictional.)

Phone numbers. Keep only digits, then keep the last 10 digits. This removes +91, a leading 0 and any spaces or dashes. A valid Indian mobile number has 10 digits.

Steps in Power BI

  1. Set Phone to Text (never a number).
  2. Add Column › Custom Column › name Phone Clean › formula: Text.End(Text.Select([Phone], {"0".."9"}), 10).
  3. Add Column › Custom Column › Phone OK › Text.Length([Phone Clean]) = 10 › set type True/False.
  4. E-mail: select the column › Transform › Format › Trim, then lowercase. Add a Custom Column E-mail OK (formula below).
  5. Pincode: set type Text. Remove spaces with Replace Values. If the source converted it to "416008.0", use Transform › Extract › Text Before Delimiter ".".
PhoneClean = Table.AddColumn(Source, "Phone Clean",
    each Text.End(Text.Select([Phone], {"0".."9"}), 10), type text),
EmailOK = Table.AddColumn(PhoneClean, "E-mail OK", each
    let e = Text.Lower(Text.Trim([E-mail])) in
    Text.Contains(e, "@") and Text.Contains(Text.AfterDelimiter(e, "@"), ".")
    and not Text.Contains(e, " "), type logical),
// codes with leading zeros, e.g. customer code 245 -> "000245"
Padded = Table.TransformColumns(EmailOK, {{"Customer Code", each Text.PadStart(Text.From(_), 6, "0"), type text}})

PIN codes and leading zeros

Now look at this: maharashtra PIN codes start with 4 (for example 411038 in Pune, 440010 in Nagpur, 431003 in Sambhaji Nagar), so they do not have leading zeros. Other codes do: customer codes, SKU codes, and PIN codes from other states in a national file. Always keep codes as Text. Use Text.PadStart to restore zeros that Excel removed.

Ravindra Bagale's Tip

Friends, many students treat Phone or Pincode as a number. Power BI then offers to Sum it, and long numbers can lose digits in scientific notation. Keep them as Text, standardise the format (remove +91, spaces and dashes), and remember that a simple e-mail check is only a data-quality flag, not full validation. Don't make this mistake!

Practice task

Clean the DeliveryPartner table phones (Amir, Raja, Salman) and add a Masked Phone column that shows fakt the last 4 digits: "XXXXXX" & Text.End([Phone Clean], 4).

Fix common mistakes

Wrong result Reason Correction
Phone 9.1E+11 Typed as number Keep Text
C02 email flagged invalid Trailing space / capitals Trim and lowercase first
Rows deleted, customers lost Filtered invalid rows Flag instead, fix at source

Practise independently

Practice task

What does Text.Select("+91 90000 00001", {"0".."9"}) return, and how many digits?

Hints
  • Keep digits only.
Solution

"919000000001" – 12 digits; Text.End(…, 10) gives 9000000001.

Think like a professional

Data-quality flags let business teams fix sources; silent deletion hides problems.

Ravi Sir's Cards for this lesson

Phone numbers, emails and PIN codes. Phone numbers stored as numbers lose leading zeros and can turn into 9.87E+09.Revision card

मराठी cards