cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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 AdressExpected postcode extracted from Full Adress
Van de Spiegellaan 99 8956 LZ Leeuwarden8956 LZ
Wilhelminakade 125 4576 HA Krimpen aan de Ijssel4576 HA
Spanjestraat 4 1648 IU Harderwijk1648 IU
Plein 31B 8643 PU Roosendaal8643 PU
Stationsstraat 3 AmsterdamNo 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)

1 ACCEPTED SOLUTION

Accepted Solutions
Solution Sage
Solution Sage

 

@StraySam 

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

Video Link

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

 

 

2020-04-27_131126.png

View solution in original post

9 REPLIES 9
Solution Sage
Solution Sage

 

@StraySam 

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

Video Link

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

 

 

2020-04-27_131126.png

View solution in original post

Super User III
Super User III

@StraySam 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.

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

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

 

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

 

@StraySam 

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors