Power QueryExercises

Cleaning phone numbers and postcodes with Power Query

How-to · By Tahir Nas · Examples run in our simulator · Updated

11 min read

To clean phone numbers in Power Query, keep them as text, strip everything except the digits with Text.Select, then apply a few explicit rules for country codes and lost leading zeros. To clean postcodes, upper-case them, remove every space and symbol, and put one space back before the last three characters. Both jobs finish with a check column, so values that still don't fit are flagged instead of quietly passed through.

Why phone and postcode columns arrive messy

Phone and postcode fields are typed by people, into forms that rarely enforce a format. One customer writes +44 (0)20 7946 0018, another 020-7946-0019, and a spreadsheet somewhere has already turned 07700900123 into the number 7700900123. Postcodes arrive in lower case, without the space, with two spaces, or with a hyphen.

None of these differences matter to a person reading the record. All of them matter to Power Query, which compares text exactly (case sensitivity in Power Query shows how far that goes). Two records for the same customer won't match on phone number unless both numbers have been reduced to one standard form first.

Keep phone numbers and postcodes as text

The first rule is about types, not text functions. A phone number looks numeric, so Power Query's automatic type detection may set the column to Whole Number. Microsoft's data types article (Microsoft Learn) explains that, for unstructured sources such as CSV and Excel files, detection inspects the first 200 rows and adds a Changed Type step for you.

Here is what that step does to a UK number:

let
    Source = #table({"Phone"}, {{"07700900123"}, {"02079460018"}}),
    AsNumber = Table.TransformColumnTypes(Source, {{"Phone", Int64.Type}})
in
    AsNumber

Result

Phone
7700900123
2079460018

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result is 7700900123 and 2079460018. The leading zero has gone. A value with a space or a bracket in it would become an error instead, and a leading + would be dropped along with the meaning it carried. Change the Changed Type step so these columns are Text, or remove them from it. The lesson choose types deliberately: identifiers are text covers the same rule for account codes and order numbers, and the companion post on keeping or removing leading zeros goes deeper on padding.

Clean phone numbers in three steps

The approach is the same for any country: reduce the value to digits, apply the national rules, then check the result.

Step 1: keep only the digits

Text.Select returns the text with every character not in the list removed (Text.Select, Microsoft Learn). The list {"0".."9"} is the ten digit characters.

let
    Source = #table({"Customer", "Phone"}, {
        {"A", "+44 (0)20 7946 0018"},
        {"B", "020-7946-0019"},
        {"C", "07700 900123"},
        {"D", "0044 7700 900456"},
        {"E", "7700900789"},
        {"F", "tel: 01632 960 001 ext. 22"},
        {"G", null}
    }),
    Digits = Table.AddColumn(Source, "Digits", each Text.Select([Phone], {"0".."9"}), type nullable text)
in
    Digits

Result

CustomerPhoneDigits
A+44 (0)20 7946 00184402079460018
B020-7946-001902079460019
C07700 90012307700900123
D0044 7700 90045600447700900456
E77009007897700900789
Ftel: 01632 960 001 ext. 220163296000122
Gnullnull

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Look at the output before writing any rules, because it shows exactly what the rules must handle:

CustomerDigitsWhat's wrong
A4402079460018Country code 44 plus the bracketed 0
B02079460019Nothing, already national format
C07700900123Nothing
D00447700900456International prefix 00 and country code
E7700900789Leading zero lost
F0163296000122Extension digits 22 glued on the end
GnullMissing, and Text.Select passes null through

Selecting digits is safer than removing characters with Text.Remove (Text.Remove, Microsoft Learn), because you can't predict every symbol people type, but you do know which characters you want to keep.

Step 2: apply the national rules in one function

The rules for this data set are short enough to write as one function. Each condition is named in the order it is tested, so a reviewer can read them like a list.

let
    Source = #table({"Customer", "Phone"}, {
        {"A", "+44 (0)20 7946 0018"},
        {"B", "020-7946-0019"},
        {"C", "07700 900123"},
        {"D", "0044 7700 900456"},
        {"E", "7700900789"},
        {"F", "tel: 01632 960 001 ext. 22"},
        {"G", null}
    }),
    ToNational = (phone as nullable text) as nullable text =>
        let
            Main = if phone = null then null else Text.Trim(Text.BeforeDelimiter(Text.Lower(phone), "ext")),
            Digits = if Main = null then null else Text.Select(Main, {"0".."9"}),
            NoIntl = if Digits = null then null
                else if Text.StartsWith(Digits, "00440") then Text.Middle(Digits, 4)
                else if Text.StartsWith(Digits, "0044") then "0" & Text.Middle(Digits, 4)
                else if Text.StartsWith(Digits, "440") then Text.Middle(Digits, 2)
                else if Text.StartsWith(Digits, "44") then "0" & Text.Middle(Digits, 2)
                else Digits,
            Padded = if NoIntl <> null and Text.Length(NoIntl) = 10 and not Text.StartsWith(NoIntl, "0") then "0" & NoIntl else NoIntl
        in
            Padded,
    National = Table.AddColumn(Source, "National", each ToNational([Phone]), type nullable text),
    Valid = Table.AddColumn(National, "Valid", each [National] <> null and Text.Length([National]) = 11 and Text.StartsWith([National], "0"), type logical),
    International = Table.AddColumn(Valid, "International", each if [Valid] then "+44" & Text.Middle([National], 1) else null, type nullable text)
in
    International

Result

CustomerPhoneNationalValidInternational
A+44 (0)20 7946 001802079460018true+442079460018
B020-7946-001902079460019true+442079460019
C07700 90012307700900123true+447700900123
D0044 7700 90045607700900456true+447700900456
E770090078907700900789true+447700900789
Ftel: 01632 960 001 ext. 2201632960001true+441632960001
Gnullnullfalsenull

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Every number except G now has an 11-digit national form and an international form: A becomes 02079460018 and +442079460018, E gets its zero back as 07700900789, and F loses the extension to become 01632960001. G is null, flagged as not valid.

What each rule does:

  1. Extensions. Text.BeforeDelimiter(..., "ext") keeps the text before "ext", so the extension digits never reach the digit filter. If your data uses "x" or "#" for extensions, add those cases. The rule cuts at the first "ext" anywhere in the text, so a note such as "text me on…" would be cut too; the Valid check then flags that row.
  2. Country code. Numbers starting 0044 or 44 lose the code and gain a leading 0. The 440 and 00440 cases handle the bracketed (0) that people write after +44, so the zero isn't doubled.
  3. Lost zero. A 10-digit value that doesn't start with 0 most likely lost its zero in a spreadsheet, so one is added back. This assumes every row is a UK number: a 10-digit number from another country would also gain a zero, so apply the rule only to rows you know are UK.
  4. Check. The Valid column expects 11 digits starting with 0. That suits this list. If yours contains shorter numbers or numbers from other countries, change the rule rather than forcing them through it.

The international column drops the leading 0 and adds +44 in front. Store one of the two forms as your key and generate the other for display.

The lessons on splitting and extracting text by delimiter and on writing custom functions in M cover the two techniques used here.

Step 3: decide what "valid" means for your data

The 11-digit rule is a format check, not proof that a number exists. It catches truncated numbers, numbers with digits typed twice and many foreign numbers that still carry their country code. It can't catch a foreign number whose digits total 10, because rule 3 pads it with a zero, so keep non-UK rows out of these rules. The GOV.UK call charges page lists what UK prefixes mean: 01 and 02 are geographic numbers, 03 are UK-wide numbers and 07 are mobile numbers. If your report only expects geographic, UK-wide and mobile numbers, you can tighten the check to those prefixes:

[Valid] and List.Contains({"01", "02", "03", "07"}, Text.Start([National], 2))

Keep the failures visible. Filter Valid = false into its own query and send that list back to whoever owns the source system.

Clean UK postcodes

The Office for National Statistics describes a postcode as an outward code of 2 to 4 characters followed by an inward code of three characters, with PO15 5RR as its example (ONS postal geography). That fixed inward length is what makes cleaning simple: strip the value down, then insert the space three characters from the end.

let
    Source = #table({"Customer", "Postcode"}, {
        {"A", " sw1a1aa"}, {"B", "SW1A 1AA"}, {"C", "ec1a  1bb"}, {"D", "w1a0ax"},
        {"E", "M1 1AE"}, {"F", "b33-8th"}, {"G", "DN551PT"}, {"H", "SW1A"}, {"I", null}, {"J", "90210"}
    }),
    Tidy = Table.AddColumn(Source, "Clean", each
        let p = Text.Select(Text.Upper([Postcode] ?? ""), {"A".."Z", "0".."9"})
        in if Text.Length(p) < 5 or Text.Length(p) > 7 then null else Text.Insert(p, Text.Length(p) - 3, " "),
        type nullable text),
    Check = Table.AddColumn(Tidy, "Looks valid", each
        [Clean] <> null
        and List.Contains({"0".."9"}, Text.At([Clean], Text.Length([Clean]) - 3))
        and Text.Select(Text.End([Clean], 2), {"A".."Z"}) = Text.End([Clean], 2)
        and List.Contains({"A".."Z"}, Text.Start([Clean], 1)), type logical)
in
    Check

Result

CustomerPostcodeCleanLooks valid
A sw1a1aaSW1A 1AAtrue
BSW1A 1AASW1A 1AAtrue
Cec1a 1bbEC1A 1BBtrue
Dw1a0axW1A 0AXtrue
EM1 1AEM1 1AEtrue
Fb33-8thB33 8THtrue
GDN551PTDN55 1PTtrue
HSW1Anullfalse
Inullnullfalse
J9021090 210false

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Rows A to G all come out in standard form (SW1A 1AA, EC1A 1BB, W1A 0AX, M1 1AE, B33 8TH, DN55 1PT) with Looks valid = true. H is only an outward code, so Clean is null. I is null. J, a US ZIP code, is reshaped to 90 210 but fails the check: it doesn't start with a letter, and its last two characters aren't letters.

How it works:

  • [Postcode] ?? "" turns null into empty text, so the steps after it never see null.
  • Text.Upper then Text.Select with letters and digits removes spaces, hyphens and stray punctuation in one go.
  • With no spaces, a full postcode is 5 to 7 characters. Anything else can't be a full postcode, so it becomes null.
  • Text.Insert puts a space at a zero-based position (Text.Insert, Microsoft Learn), here three characters from the end.
  • The check confirms the shape: a letter first, and an inward code of a digit followed by two letters.

This is a shape check. It doesn't prove the postcode exists, and it accepts some combinations that are never issued. If you need that certainty, merge against a postcode list from an official source rather than adding more rules.

Use the cleaned column as the key

Once both columns are standardised, use the clean versions everywhere: for merges with other systems, for duplicate checks and for counts by area. The outward code is useful on its own for regional reports, and it is simply the text before the space: Text.BeforeDelimiter([Clean], " ").

Keep the original column next to the clean one while you are building the query, so you can compare them row by row. Remove it, or move it to the end, once you trust the rules. The guide on splitting columns for names, codes, addresses and lists and the text functions cheat sheet list the other functions that help with this kind of work. Phone and postcode cleaning is usually one part of a larger contact clean-up, which the post on cleaning a messy CRM export walks through end to end.

Practise cleaning phone numbers and addresses

The exercise keep only the digits of phone numbers practises Text.Select on a short list, and build a one-line address that skips missing parts covers the next step for address data. Both run in the browser on Power Query Exercises and check your result against the expected table.

Frequently asked questions

Should I store phone numbers in +44 format or with a leading 0?

Pick one form as the stored key and use it everywhere. The international +44 form works across countries. The national form is easier for people to read. Generating one from the other is a one-line step, as the example above shows.

How can I check that a postcode really exists?

A shape check can't tell you that. Merge the cleaned postcodes against a reference list of real postcodes and treat the rows with no match as the ones to review. An anti join returns exactly those rows.

What about phone numbers from other countries?

Write a separate rule set per country, keyed on a country column, and don't force every number through the UK rules. Numbers that already start with + and another country code can be kept as digits with the + restored.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.