Forum Discussion
Extract postcode from Text
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)
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
9 Replies
- VijayP
Community Champion
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
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
- VijayP
I think I coundnt understand your question. can you please rephrase this
- nandukrishnavs
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.
