Process a string to extract parts of words

[stchln]

Hello,

I need to extract from a string part of the words separated with a comma.

Input:

Col1

AA, AB-C-b, AC-DD, AD-EE-e, AE-FF-B, AG-HH xx

What I would like to get : to remove text starting 2nd occurrence of '-' (when it does exists) up to the separator ',' or blank or end of line

Col2

AA,AB-C,AC-DD,AD-EE,AE-FF,AG-HH

What I am able to do:

1. To use Power Query to remove noise characters with a Text.Select([col1],{"0".."9","A".."Z","-",","})

2. To Split the string using Text.Split()

3. To extract relevant substring using below formula

if List.Count(Text.PositionOfAny([col1], {"-"}, Occurrence.All))=0
then
	[col1]
else
  Text.Combine({
	Text.BeforeDelimiter([col1],"-"),
	Text.Start(Text.AfterDelimiter([col1],"-"),
		Text.PositionOfAny(
	        Text.AfterDelimiter([col1],"-"), {"-"," "}))
               },"-")

But, I am not able to combine 1, 2 and 3 to get a single string at the end...

I think I need some help here

[Zubair_Muhammad]

@stchln 

Try this custom column as well

=let mylist=Text.Split([Col1],",")
in 
Text.Combine(
List.Transform(mylist, each Text.BeforeDelimiter(_,"-",1))
,
",")

[stchln]

Hi Zubair_Muhammad,

Thanks for your help. Your solution is not capturing the part of the word after the first '-' so not matching my need but it appears as more clean than what I started to do.

[AlB · Accepted solution]

Hi @stchln 

Have a look at this code in a blank query to see the steps. I've placed each of your three steps in custom columns so that it is easier to follow. You've done most of the work and actually your code for the last step should be in the second argument of the List.Transform( ) function in #"Added Custom3". It would thus be applied to each element in the list, which I believe is in essence what you were missing. I tried this, but got some errors. I had some problems to follow your code so I put together something else for that step that I think it's simpler. Do check it anyway because I am not sure it does exactly what you need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTUUXB00nXWTQLSzrouLkDKRdfVVTcVyHDVdXPTdQIy3HU9PBQqKpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "Step1", each Text.Select([col1],{"0".."9","A".."Z","-",","})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Step2", each Text.Split([Step1],",")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Final", each List.Transform([Step2], each let pos_ = try Text.PositionOfAny(_,{"-"},Occurrence.All){1} otherwise null, res_ = if pos_=null then _ else Text.Start(_, pos_) in res_)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Final", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[stchln]

Hi Super User III

This is doing exactly what I need!! Thanks a lot.

I suspected I have to use List.Transform but were not able to manipulate. Thanks again

About the Kudos, I tried to apply but not of the procedure so help this will reach you

stchln

[AlB]

Ah! King @Zubair_Muhammad  is back... and as usual his solution is more elegant

@stchln :

I believe @Zubair_Muhammad's solution works fine if you apply it after your "remove noise characters" step, i.e., directly to column [Step1] in my code above

As for the Kudos, you can just click on the thumbs-up (Like) icon situated immediately under each post

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers