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"
AlexisOlson
4 years agoSuper User
I like it. I came up with something similar.
List.Max(
List.Select(
Text.SplitAny(
Text.Select( [Address], { "0".."9", " ", "," } ),
" ,"
),
each Text.Length(_) = 5
)
)MuthalibAbdul
2 years agoHelper II
This solution worked like a charm!! thanks