Forum Discussion
Solved
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, Barcelona, España |
| Calle de Antonia Rodríguez Sacristán, 31, 28044 Madrid, España |
| Plaza Mayor, 6, 29570 Villafranco de Guadalhorce, Málaga, España |
| Av. de Europa, 16, 03503 Benidorm, Alicante, España |
| Calle de Villaamil, 78, 28039 Madrid, España |
| 191 Rue du Faubourg Saint-Antoine, 75011 Paris |
| Calle Asturias, 11, 33004 Oviedo, Asturias, España |
| Crta Circunvalación S/N C.C. Costasol, Local 7, 29620, Málaga, España |
| Calle Begonia, 1, 21005 Huelva, España |
| Calle Crucero 34, Urbanizacion Castillo del Espiritu Santo, 11540 Sanlúcar de Barrameda, Cádiz, España |
| Calle de Joaquín Velasco Martín, 15, 47014 Valladolid, España |
| Carrer Castellón, 1 Izquierda, 46940 Manises, Valencia, España |
| Carrer del Pintor Pinazo, 1, 46940 Manises, Valencia, España |
| Calle de María de Molina, 22, 28006 Madrid, España |
| Rúa Alfonso XII, 12, 36800 Redondela, Pontevedra, España |
| Camino San Bartolomé de Geneto, 91, 38296 San Cristóbal de La Laguna, Santa Cruz de Tenerife, España |
| Carretera del Aeropuerto, Km 4,250 junto supermercado Piedra camino del Aeroclub, 14005 Córdoba, España |
| Avinguda de Gabriel Alomar, 5, 07006 Palma, Illes Balears, España |
| Carrer Gran de Gràcia, 115, 08012 Barcelona, España |
| Av. de las Playas, 49, 35510 Tías, Las Palmas, España |
| Av. San Martín de Valdeiglesias, 17X, 28922 Alcorcón, Madrid, España |
| Av. Ciudad de Almería, 65, 30010 Murcia, España |
| Calle Mocholí, 31110 Noáin, Navarra, España |
The postal code is the five digit number in the string:
| Carrer Riera de Sant Jordi, 3, 08390 Montgat, Barcelona, España |
I've been having a go but failing dismally.
Any solution will be very welcome!
Thanks,
Paul.
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"
11 Replies
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"Works like a charm! Thanks!
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 ExtractedI 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
Hi 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.
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
Thanks HotChilli . Unfortunately it is delivering inconsistent results, probably because the postal code has a cunning way of appearing in different delimeter locations
