Forum Discussion
PaulDBrown
Community Champion
4 years agoExtract 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
Community Champion
4 years agoHi Paul, PaulDBrown
I was definitely aware it wasn't working on all rows. It worked on about 95% of the test data which I thought was pretty good for a quick response. I thought other folk would chime in too.
I've seen that regex solution from smpa01 before and it's fantastic.