Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
DataZoe
Employee
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. 

DataZoe_2-1611783624730.png

 

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"

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

Try using Calculated column by Examples.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

DataZoe
Employee
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. 

DataZoe_2-1611783624730.png

 

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"

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.