Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Proud to be a Super User!
Paul on Linkedin.
Solved! Go to Solution.
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"
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.
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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!
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.