// 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"