Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
VijayP
Super User
Super User

 

@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

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


View solution in original post

9 REPLIES 9
nandukrishnavs
Super User
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
🙂


Regards,
Nandu Krishna

Anonymous
Not applicable

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.

VijayP
Super User
Super User

 

@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

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

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

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

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.




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.