Extract postal code from address

[PaulDBrown]

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.

[HotChilli]

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.

[CNENFRNL]

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

[AlexisOlson]

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]

This solution worked like a charm!! thanks

[smpa01 · Accepted solution]

@PaulDBrown 

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"

https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p...

https://community.powerbi.com/t5/Community-Blog/How-to-use-JavaScript-inside-power-query-for-data-ex...

[PaulDBrown]

Works like a charm! Thanks!

[HotChilli]

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

[PaulDBrown]

Thanks @HotChilli . Unfortunately it is delivering inconsistent results, probably because the postal code has a cunning way of appearing in different delimeter locations