Contacts cleanup with a custom function
Messy phone numbers and postcodes, cleaned by small custom M functions you can reuse across queries.
advanced · Skills: Custom functions, Text.Select, Null-safe functions · Updated 2026-11-04
Data files
- contacts.csv — ContactID, Name, Phone (many formats, n/a, extensions), Postcode (case and spacing vary).Download CSV (288 B)
Queries
Validated in the Power Query Exercises simulator against the files above. Paste them into Excel or Power BI yourself; the header comments explain each source binding.
Clean contacts clean-contacts.pq
Download .pqfnPhone keeps an 11-digit UK mobile or returns null; fnPostcode upper-cases and spaces the code. Returns 7 rows: ContactID, Name, Phone, Postcode, PhoneClean, PostcodeClean.
// Clean contacts
// Pack: Contacts cleanup with a custom function (Power Query Exercises, https://powerqueryexercises.com/downloads/contacts-cleanup-function)
//
// Source bindings
// In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or
// Power BI Desktop the first steps of this query do that binding instead:
// Contacts the pack file contacts.csv -> File.Contents(PackFolder & "contacts.csv")
// Set PackFolder to the folder where you extracted the pack (keep the trailing backslash).
//
// Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns
// 7 rows with columns ContactID, Name, Phone, Postcode, PhoneClean, PostcodeClean.
// It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook.
// Independent practice material; not affiliated with or endorsed by Microsoft.
let
PackFolder = "C:\PQE\contacts-cleanup-function\",
Contacts = File.Contents(PackFolder & "contacts.csv"),
fnPhone = (raw as nullable text) as nullable text =>
let
main = if raw = null then "" else Text.BeforeDelimiter(Text.Lower(raw), "ext"),
digits = Text.Select(main, {"0".."9"}),
national = if Text.StartsWith(digits, "44") then "0" & Text.Range(digits, 2) else digits
in
if Text.Length(national) = 11 then national else null,
fnPostcode = (raw as nullable text) as nullable text =>
let compact = Text.Upper(Text.Remove(raw ?? "", {" "}))
in if Text.Length(compact) < 5 then null else Text.Start(compact, Text.Length(compact) - 3) & " " & Text.End(compact, 3),
Source = Table.PromoteHeaders(Csv.Document(Contacts, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]),
#"Clean phone" = Table.AddColumn(Source, "PhoneClean", each fnPhone([Phone]), type text),
#"Clean postcode" = Table.AddColumn(#"Clean phone", "PostcodeClean", each fnPostcode([Postcode]), type text)
in
#"Clean postcode"
Follow along in a host
Follow along in Excel
- Download the pack and extract the zip, for example to C:\PQE\<pack name>\.
- In Excel (Microsoft 365 or 2016+ on Windows), go to Data → Get Data → From Other Sources → Blank Query.
- In the Power Query Editor choose Home → Advanced Editor, select everything and paste the contents of the .pq file.
- Change the PackFolder line to the folder you extracted to, then choose Done.
- If Excel asks about privacy levels or file access, allow access to that folder. Then choose Home → Close & Load.
Follow along in Power BI Desktop
- Download the pack and extract the zip.
- In Power BI Desktop choose Home → Transform data to open the Power Query Editor, then Home → New Source → Blank Query.
- Choose Home → Advanced Editor, paste the contents of the .pq file and change the PackFolder line to your folder.
- Choose Done, check the preview against the expected rows in the query header, then Close & Apply.
Then try this
- In the editor, right-click the query and choose Create Function only after you've tested the steps on one value.
- Reuse fnPhone in another query by moving it into its own query and referencing it by name.
Related lessons
Original fictional data, free to use for learning and teaching. Power Query Exercises is not affiliated with or endorsed by Microsoft.