Extract a reference from a column based on specific criteria

[Nigel_Mayhew1]

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

[PhilipTreacy · Accepted solution]

@Nigel_Mayhew1 

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 null

NOTE 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]

Hi @Nigel_Mayhew1 

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

[PhilipTreacy]

Hi @Nigel_Mayhew1 

Those particular cases causing issues weren't in the original data but no problem, give me 10 minutes to work on it.

Phil

[PhilipTreacy]

Hi @Nigel_Mayhew1 

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 null

Regards

Phil

[Nigel_Mayhew1]

Hi 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