Forum Discussion
Helper IExtract a reference from a column based on specific criteria
Hi there,
I need to extract a specific reference from a column:
| Reference | Result |
| X201227638 | null |
| ZRET19/0014457 | ZRET19/0014457 |
| ZRET23/0003605 ( X201212911-1-Madone 838954920) | ZRET23/0003605 |
| SB_X201463782 ZRET23/0010338 | ZRET23/0010338 |
| X201436502-1 ZRET22/0004334 - TA-N100728285 | ZRET22/0004334 |
| Leergut Z201294363-1-Fuerstenfeldbruck | null |
| ( X201212911-1-Madone 838954920 ZRET14/55597 ) | ZRET14/55597 |
The string always starts with ZRET (sometimes Zret or zret).
Can someone please help?
Thanks,
Nigel
OK try this, works for me
= try List.Select(Text.Split(Text.Range([Reference], Text.PositionOf(Text.Upper([Reference]), "ZRET")), " "), each Text.Contains(_ , "ZRET")){0} otherwise nullNOTE that in the 2nd lot of data you provided the 3rd line that begins Durchg mit gives a null becuase the code has a typo - it's ZRTE instead of ZRET. I corrected this in my new example.
Regards
Phil
6 Replies
- PhilipTreacy
Super User
Download PBIX file with example below
Create a Custom Column in Power Query with this
= try List.Select(Text.Split([Reference], " "), each Text.Contains(_ , "ZRET")){0} otherwise nullRegards
Phil
Nigel_Mayhew1Hi Philip,
Thanks for your response!
It mostly works well, but I still have some issues with the results.
Here are a few examples:
Reference Result DURCHG. MIT TA-N100640929_ ZRET23/000781 ZRET23/000781 DURCHGEF. MIT TA-N100646558-ZRET19/00144254 TA-N100646558-ZRET19/00144254 Durchg mit TA-N100642382- ZRTE18/0011958 null FAKTURIERT MIT TA-N100642382 _ZRET18/0011958 _ZRET18/0011958 FAKTURIERT MIT TA-N100642383 ZRET19/0014424 ZRET19/0014424 FALSCH ZRET23/0010338 SB_X201463782 _ ZRET23/0012143 ZRET23/0010338 Fakaturiert mit TA-N100632170 ZRET19/0014457 ZRET19/0014457 Fakturiert mit TA-N100632168 ZRET23/0003782 ZRET23/0003782 TA-15559999ZRET00/0013631 TA-15559999ZRET00/0013631 Can you please help?
Best,
Nigel
- PhilipTreacy
Those particular cases causing issues weren't in the original data but no problem, give me 10 minutes to work on it.
Phil
- PhilipTreacy
OK try this, works for me
= try List.Select(Text.Split(Text.Range([Reference], Text.PositionOf(Text.Upper([Reference]), "ZRET")), " "), each Text.Contains(_ , "ZRET")){0} otherwise nullNOTE that in the 2nd lot of data you provided the 3rd line that begins Durchg mit gives a null becuase the code has a typo - it's ZRTE instead of ZRET. I corrected this in my new example.
Regards
Phil
- Nigel_Mayhew1
Hi Phil,
That's absolutely perfect. Thank you!
Would you be kind enough please to just tell me what exactly I must change in the code if in the future I needed to look for strings other than ones starting with 'ZRET'?
For example if the strings were to begin with X5, L8 and Z1 and are 10 characters long.
Very much appreciated!
Best,
Nigel
- PhilipTreacy
If you are just looking for one type of string at a time you just need to chnage any occurrence of ZRET in my code to X5, L8 etc.
However if you need to look for ZRET and X5 and L8 etc at the same time, you'll need different code.
If you can supply some example data I can show you.
Regards
Phil