Extract a specific number of charcherts if a condition is met

[Nigel_Mayhew1]

Hi there,

I'd be very grateful if someone'd help with this:

I've a column called Reference. I need to extract a specific number of charachters (10) if the rows of this column contain a value starting with either an X or a Z.

For example, the references that I want to extract could be at the start, the middle or the end:

348964516-Z100158161

X400004308-1-65655687

(X100069554_9998

FullPPL_X300094361

What's important is that I extract the X or the Z in addition to the 9 charachters that follows (total 10 charachters including the letter).

This what the result should look like:

Z100158161

X400004308

X100069554

X300094361

Thanks, Nigel

[Ahmedx · Accepted solution]

write the code in language m in power query

[Ahmedx · Accepted solution]

pls try this

[
t1 = Splitter.SplitTextByCharacterTransition({"X","Z"},{"0".."9"})([Column1]),
t2 =  Text.End( t1{0},1)& Text.Start( t1{1},9),
Result= if List.Count(t1)>1 then t2 else null

][Result]

[Ahmedx · Accepted solution]

pls try

[Ahmedx]

or this

[a = Text.BetweenDelimiters([Reference],"ZRET"," ",RelativePosition.FromStart),
b = if a <>"" then "ZRET"& a else null][b]

[Ahmedx · Accepted solution]

write the code in language m in power query

[Nigel_Mayhew1]

Hi Ahmed,

It worked really well. Thank you!

How can i please eleminate the errors deriving from cells containing values such as this?:

ZRET14/0071470

It would be great to deal with this in the same step.

Much appreciated, Nigel

[Ahmedx · Accepted solution]

pls try this

[
t1 = Splitter.SplitTextByCharacterTransition({"X","Z"},{"0".."9"})([Column1]),
t2 =  Text.End( t1{0},1)& Text.Start( t1{1},9),
Result= if List.Count(t1)>1 then t2 else null

][Result]

[Nigel_Mayhew1]

Hi Ahmed,

I now have another case where i need to extraxt 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

Can you please help?

Thanks,

Nigel

[Ahmedx]

pls try this

[
    a = if Text.Contains([Reference], "ZRET") then Splitter.SplitTextByAnyDelimiter({" ","-",")","("})([Reference]) else {null},
b= List.Min( List.Select(a, (x)=> Text.Contains(x, "ZRET")))
][b]

[Nigel_Mayhew1]

Hi Ahmed,

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	ZRET19/00144254
Durchg mit TA-N100642382- ZRTE18/0011958	null
Durchgef. mit TA-N100640917_ ZRET19/0014457	ZRET19/0014457
Durchgef. mit TA-N100646527 - ZRET23/0003958	ZRET23/0003958
Durchgeführt mit TA-100632927 TT-10213893 ZRET23/0003605	ZRET23/0003605
Dürchgeführ mit TA-N100640935_ ZRET23/0003782	ZRET23/0003782
ERFASST ZRET23/0013486 GESCANT ZRET23/0012163/BZ:162	ZRET23/0012163/BZ:162
Erfasst Zweck Fakturation SB	null
FAKTURIERT MIT TA-N100632169 ZRET23/0003781	ZRET23/0003781
FAKTURIERT MIT TA-N100642382 _ZRET18/0011958	_ZRET18/0011958

Can you please help?

Best,

Nigel

[Ahmedx · Accepted solution]

pls try

[Nigel_Mayhew1 · Accepted solution]

Hi Ahmed,

Many thanks!

When you have a couple of minutes, I'd very grateful if you'd explain how this M code works, so i can adjust it to my needs in the future (perhaps i'll need to extract strings starting with something else other than ZRET):

[
a = Splitter.SplitTextByAnyDelimiter({" ","-",")","(","_"})([Reference]),
b = List.Select( a, (x)=> List.Count(
Splitter.SplitTextByCharacterTransition({"/"},{"0".."9"})(x))>1),
c = List.Transform(List.Transform(b, (x)=> Text.SplitAny(x,"/")),(x) => x{0}&"/"&x{1})
][c]

Best,

Nigel

[Ahmedx]

// Step 1: Split the text in the [Reference] column by any specified delimiter (space, "-", ")", "(", "_").
a = Splitter.SplitTextByAnyDelimiter({" ", "-", ")", "(", "_"}) ([Reference]),

// Step 2: Filter the elements in list `a`, selecting only items that contain at least one "/" character followed by a sequence of numbers.
// This is done by checking each element in `a` to see if splitting by a "/" and a sequence of digits (0-9) results in multiple parts.
b = List.Select(a, (x) => List.Count(
Splitter.SplitTextByCharacterTransition({"/"}, {"0" .. "9"})(x)) > 1),

// Step 3: Transform each item in `b` by splitting it by the "/" character.
// Then, reconstruct each item by joining the first two parts from the split with "/" in between.
c = List.Transform(
List.Transform(b, (x) => Text.SplitAny(x, "/")),
(x) => x{0} & "/" & x{1}
)

[Nigel_Mayhew1]

Magic! Thanks very much!

[Mykhailo_Fl]

Column here is for visualyzing purpose of the SEARCH output.
Value required is in Column 2, see the DAX for it