Forum Discussion
Extract postal code from address
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"
I'd resort to regex to deal with string; since such a solution is already in place, I came up with a solution with native PQ functions.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVTLbhtHEPyVhs8bZZZcvo7UwlHkiApBO4IBw4fm7oieYDgj9+4QML8mOuagQ6Cbr/tjqV4qgCyKQAACBDn9qK6q7k+f3pQsYoVWzgpTbek9h5beRaldRsOMzHQ4M7SIod1wm9E5S2V9DJzR2+aOu3/4zedMi3hvNXse2hgc0yrW0j1skt2jYCWuabv7gIJ5RoOpKQpacC2u/rHK0vOe8fItSkZjRM5GE0M3znu+FQ5V1A4XiWv2XyJwZLTo7j1vXoCZ78408G2SeIenHJXMcGSGdG6Dq6NsM5p7V2FOe2KKviVvnc9oMu0RD2evIs5nOa0SchL9wmkdk2wwrwvtT0qEC2gwGZk8pyWDg2dN5k2bxHEDeKBkODSmoN93ztYxe/b2AzhpmUonVQo79ly57jHQ+5+vqTwrz6iMTctNBOCrWLGnibI3HpgTFB1AnNuNigUMiM6NGdGvyfrdq7GlpMpKpGGR0R+y5uD2wBADldy0oEu18ZrnxLWpd1HU4UaF0R+++16xKLdwkPDW1uhSdve125/Q4F3kr6l7CHRjPTfQfsHS4jeKjjIqJiYv6AbBXEf/UpYnTys06z2IQhJd7r8mmFwbF+MZYC0wRGNBM8rYULmjufsiOtYSikbRL97Hnq7/WeFpFkDvHvrtWgCsLs9g0NvKjF+11ar7zvDobQxNpI+Xl2iJ+OEYCbSCRwJAocgSW2l3tpajvlsXotKubLfRx233d787NliVZaaem8IgfUzZr+fjGrZBzBXjs0kKUkVkVX6vDx+QLO72aGfAUnu4HZ7msMhdsqJNfttSkQ1Ghv5MYI+adGdla6WCYGBSUVN1APpfZuXTGqMW6sSye5Q6ro9W24VNqnsqL3gtTjMxHuNgwBVmoowu2W+RdwnyGxDgLcvLXTpIe4Gj0leS7q9evFytZaYmH5w6dE+3BY4kXKtvuqTFDGSORrmhDxAZf1zpo2JojnOV7ycj94eGfW3dBkAPt2DyUW0xGwwwVYUT11v3NYdordKBibq/uh7EojmuJgbAMQGYRZIThlzE6kv03YOe4xyR17G7d+hzzTvdzWcpn/8F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]),
Extracted = Table.AddColumn(Source, "Postal Code", each List.Select(Text.SplitAny([Address], Text.Remove([Address], {"0".."9"})), each _<>"" and Text.Length(_)=5){0})
in
Extracted
I like it. I came up with something similar.
List.Max(
List.Select(
Text.SplitAny(
Text.Select( [Address], { "0".."9", " ", "," } ),
" ,"
),
each Text.Length(_) = 5
)
)This solution worked like a charm!! thanks
Alexis,
Thanks for the simpler solution; works like a champ except for one interesting case:
Address String ="12345 Main ST Shakopee MN 55379 1234"
I made a change to your solution as follows:
=Text.Middle(List.Max(
List.Select(
Text.SplitAny(
Text.Select( [Address], { "0".."9", " ", ",","-" } ),
" ,"
),
each Text.Length(_) >= 5
)
),0,5)Clarification to my reply:
The Address String was ="12345 Main ST Shakopee MN 553791234"
With no space or dash character
Update #2:
With Address String ="96412 Main St Shakopee MN 553791234"
the ListMax() function returns "96412" instread of the expected "55379"
I updated the part: Text.Select( [Address], { "0".."9", " ", ",","-" } ),
with: Text.Select( Text.End([Address],12), { "0".."9", " ", ",","-" } ),
