Forum Discussion
Extract ZIP from Address using DAX or PowerQuery
Hello All,
Having an issue parsing out a ZIP code from an address field. The data source does not have a dedicated ZIP code field and we need to extract the zip code from a full mailing address. For example, if we have a column "address" with the following text, we want to extract "20500" into a new calculated column. We must also consider that the format of the address entries are not necessary consistent (ie the commas are not always in the same spot, address not always in the same order).
Ideally what we want to do is to extract a 5 digit number from a string.
"1600 Pennsylvania Avenue NW, Washington, DC 20500, United States" = "20500" using a formula
Thanks in advance,
MK
Anonymous You can do some python or R regex searching and include that in your power query as an option. I would approach it a little simplier first and parse out the data to right of the first comma, then simply keep only numeric values.
You can see that by pasting this into a blank query's advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzMFAISM3LK67MKUvMy0xUcCxLzStNVfAL11EITyzOyMxLL8nP01FwcVYwMjA1MNBRCM3LLElNUQguSSxJLVaK1SHDEEK6qG0zbjNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Column1 - Copy", each Text.AfterDelimiter(_, ","), type text}}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1 - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1 - Copy.1", "Column1 - Copy.2"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1 - Copy.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1 - Copy.2.1", "Column1 - Copy.2.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition1",{"Column1 - Copy.1", "Column1 - Copy.2.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1 - Copy.2.1", "Zip"}})
in
#"Renamed Columns"
3 Replies
- DataZoe
Microsoft Employee
Anonymous You can do some python or R regex searching and include that in your power query as an option. I would approach it a little simplier first and parse out the data to right of the first comma, then simply keep only numeric values.
You can see that by pasting this into a blank query's advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzMFAISM3LK67MKUvMy0xUcCxLzStNVfAL11EITyzOyMxLL8nP01FwcVYwMjA1MNBRCM3LLElNUQguSSxJLVaK1SHDEEK6qG0zbjNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Column1 - Copy", each Text.AfterDelimiter(_, ","), type text}}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1 - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1 - Copy.1", "Column1 - Copy.2"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1 - Copy.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1 - Copy.2.1", "Column1 - Copy.2.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition1",{"Column1 - Copy.1", "Column1 - Copy.2.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1 - Copy.2.1", "Zip"}})
in
#"Renamed Columns" - parry2k
Super User
Anonymous since your address is not always the same, check this post and see if you can tweak it to meet your need.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- Ashish_Mathur
Hi,
Try using Calculated column by Examples.
