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.
Hi Everyone,
I have a column where date contains streetname, housenumber, postcode code and city.
Is it possible to extract the postcode (4 digits & 2 non-digits) in PowerQuery?
Id be happy if someone could teach me how to do it.
Thanks in advance!
Sam
Full Adress | Expected postcode extracted from Full Adress |
Van de Spiegellaan 99 8956 LZ Leeuwarden | 8956 LZ |
Wilhelminakade 125 4576 HA Krimpen aan de Ijssel | 4576 HA |
Spanjestraat 4 1648 IU Harderwijk | 1648 IU |
Plein 31B 8643 PU Roosendaal | 8643 PU |
Stationsstraat 3 Amsterdam | No Postcode |
Formula created by PowerBI:
=let splitObjectOmschrijving = Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"})([Full Adress]) in Text.Start(splitObjectOmschrijving{2}?, 7)
Solved! Go to Solution.
@Anonymous
I hope this is the answer you would like to have
you need to open powerquery editor
select Addcolumn Tab
in that use Column From Example and follow the instructions given in this short video
Regards
Vijay Perepa
If you find this as solution please mark it as solution and share your Kudos
you can watch my Power BI and Excel Videos at www.youtube.com/perepavijay
Proud to be a Super User!
@Anonymous Are you trying to extract postcode (4 digits & 2 non-digits) from Results column or Full Address column?
If you have a proper structure in the base column, you could use the split column function.
checkout this blog- https://www.sharepointsky.com/split-columns-in-power-bi/
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Hi @nandukrishnavs
Thank you for your reply.
i want to extract the code from full adress, the results column is the result i want to see.
Unfortunately, the data is not proper. Sometimes there is no postcode. It is also possible that the streetname and city contains several words.
@Anonymous
I hope this is the answer you would like to have
you need to open powerquery editor
select Addcolumn Tab
in that use Column From Example and follow the instructions given in this short video
Regards
Vijay Perepa
If you find this as solution please mark it as solution and share your Kudos
you can watch my Power BI and Excel Videos at www.youtube.com/perepavijay
Proud to be a Super User!
Hi @VijayP ,
Thank you for the solution, it helped me to extract the postcode from "Full Adress".
The result column is what i hope to extract from the column "Full Adress". So its done Manually.
Thanks in advance,
Sam
I think I coundnt understand your question. can you please rephrase this
Proud to be a Super User!
Hi @VijayP,
This is the data i have:
Full Adress |
Van de Spiegellaan 99 8956 LZ Leeuwarden |
Wilhelminakade 125 4576 HA Krimpen aan de Ijssel |
Spanjestraat 4 1648 IU Harderwijk |
Plein 31B 8643 PU Roosendaal |
Stationsstraat 3 Amsterdam |
And this is the result i want:
Results |
8956 LZ |
4576 HA |
1648 IU |
8643 PU |
No Postcode |
I hope this is more understandable.
Solution is Here
IF this is the solution then please mark this a solution and share your Kudos
Proud to be a Super User!
Hi @VijayP,
Thank you so much for your help.
Unfortunately, your last post doesnt work if there is no housenumber:
Full Adress |
Van de Spiegellaan 99 8956 LZ Leeuwarden |
Wilhelminakade 125 4576 HA Krimpen aan de Ijssel |
Spanjestraat 4 1648 IU Harderwijk |
Plein 31B 8643 PU Roosendaal |
Stationsstraat 3 Amsterdam |
Portugallaan 2567 HA Enschede |
Results |
8956 LZ |
4576 HA |
1648 IU |
8643 PU |
No Postcode |
2567 HA |
@Anonymous
This is a kind of Machine Learning ability. In this you need to update/feed Power Query with few more examples, it works for any kind of data ., you need to make the engine to understand the complexity of the data and what is outcome you require.
Proud to be a Super User!
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.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |