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
AsNumberResult
| 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
DigitsResult
| Customer | Phone | Digits |
|---|---|---|
| A | +44 (0)20 7946 0018 | 4402079460018 |
| B | 020-7946-0019 | 02079460019 |
| C | 07700 900123 | 07700900123 |
| D | 0044 7700 900456 | 00447700900456 |
| E | 7700900789 | 7700900789 |
| F | tel: 01632 960 001 ext. 22 | 0163296000122 |
| G | null | null |
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:
| Customer | Digits | What's wrong |
|---|---|---|
| A | 4402079460018 | Country code 44 plus the bracketed 0 |
| B | 02079460019 | Nothing, already national format |
| C | 07700900123 | Nothing |
| D | 00447700900456 | International prefix 00 and country code |
| E | 7700900789 | Leading zero lost |
| F | 0163296000122 | Extension digits 22 glued on the end |
| G | null | Missing, 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
InternationalResult
| Customer | Phone | National | Valid | International |
|---|---|---|---|---|
| A | +44 (0)20 7946 0018 | 02079460018 | true | +442079460018 |
| B | 020-7946-0019 | 02079460019 | true | +442079460019 |
| C | 07700 900123 | 07700900123 | true | +447700900123 |
| D | 0044 7700 900456 | 07700900456 | true | +447700900456 |
| E | 7700900789 | 07700900789 | true | +447700900789 |
| F | tel: 01632 960 001 ext. 22 | 01632960001 | true | +441632960001 |
| G | null | null | false | null |
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:
- 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. - Country code. Numbers starting
0044or44lose the code and gain a leading 0. The440and00440cases handle the bracketed(0)that people write after +44, so the zero isn't doubled. - 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.
- Check. The
Validcolumn 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
CheckResult
| Customer | Postcode | Clean | Looks valid |
|---|---|---|---|
| A | sw1a1aa | SW1A 1AA | true |
| B | SW1A 1AA | SW1A 1AA | true |
| C | ec1a 1bb | EC1A 1BB | true |
| D | w1a0ax | W1A 0AX | true |
| E | M1 1AE | M1 1AE | true |
| F | b33-8th | B33 8TH | true |
| G | DN551PT | DN55 1PT | true |
| H | SW1A | null | false |
| I | null | null | false |
| J | 90210 | 90 210 | false |
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.UpperthenText.Selectwith 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.Insertputs 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
- data types article (Microsoft Learn) (learn.microsoft.com)
- Text.Select, Microsoft Learn (learn.microsoft.com)
- Text.Remove, Microsoft Learn (learn.microsoft.com)
- GOV.UK call charges page (gov.uk)
- ONS postal geography (ons.gov.uk)
- Text.Insert, Microsoft Learn (learn.microsoft.com)
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
- Fuzzy matching in Power Query: standardise inconsistent namesStandardise inconsistent product names in Power Query: normalise text first, let fuzzy merge suggest matches, then keep checked matches in a mapping table.
- How to split full names into first and last names in Power QuerySplit a full name column into first and last names in Power Query, with rules for middle names, single names, 'Last, First' order, titles and suffixes.
- Keep or remove leading zeros in Power QueryKeep leading zeros in Power Query by typing codes as text, put lost zeros back with Text.PadStart, and remove them safely with Text.TrimStart.
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.