cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Process a string to extract parts of words

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Process a string to extract parts of words

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 

SU18_powerbi_badge

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Process a string to extract parts of words

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 

SU18_powerbi_badge

View solution in original post

Highlighted
Super User III
Super User III

Re: Process a string to extract parts of words

@stchln 

 

Try this custom column as well

 

=let mylist=Text.Split([Col1],",")
in 
Text.Combine(
List.Transform(mylist, each Text.BeforeDelimiter(_,"-",1))
,
",")
Try my new Power BI game Cross the River
Highlighted
Helper I
Helper I

Re: Process a string to extract parts of words

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.

Highlighted
Helper I
Helper I

Re: Process a string to extract parts of words

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

Highlighted
Super User III
Super User III

Re: Process a string to extract parts of words

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 

SU18_powerbi_badge

   

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.