Forum Discussion
PaulDBrown
4 years agoCommunity Champion
Extract postal code from address
Good afternoon,
I need to "extract" (copy) the postal codes from an address into a new column.
Here is a sample of the data:
Address
Carrer Riera de Sant Jordi, 3, 08390 Montgat, Barcel...
- 4 years ago
let regex=let fx=(input)=> Web.Page( "<script> var x='"&input&"'; // this is the input string for regex var b=x.match(/\d{5}/gm); // specify the desired regular expression inside string.match() //https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/match document.write(b); </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text] in fx, Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/Extract-postal-code-from-address/m-p/2218442#M65740"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(5) > * > TR > :nth-child(1)"}}, [RowSelector="TABLE:nth-child(5) > * > TR"]), #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each regex([Column1])) in #"Added Custom"
HotChilli
4 years agoCommunity Champion
Hello PaulDBrown ,
This is absolutely not the best and final answer but I don't really get to use this function often.
Text.BetweenDelimiters([Address], ", ", " ", 1, 0)
Hopefully others will provide a more complete answer.
Good luck
- PaulDBrown4 years agoCommunity Champion
Thanks HotChilli . Unfortunately it is delivering inconsistent results, probably because the postal code has a cunning way of appearing in different delimeter locations