Ravindra BagaleCourses & study guides

7. Data Cleaning A–Z in Power Query

7.13 Phone Numbers, E-mails and PIN Codes

By Last updated:

Before

Customer Phone E-mail Pincode
Ravina +91 98220 12345 Ravina@Example.com· 411038
Raja 098220-54321 raja.example.com 431 003
Salman 91-9822011111 SALMAN@EXAMPLE.COM 416008.0
Rani 12345 rani@example 440010

After

Customer Phone Phone OK E-mail E-mail OK Pincode
Ravina 9822012345 TRUE ravina@example.com TRUE 411038
Raja 9822054321 TRUE raja.example.com FALSE 431003
Salman 9822011111 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

Aata he bagha: 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).