Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stchln
Resolver I
Resolver 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
AlB
Super User
Super User

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
Zubair_Muhammad
Community Champion
Community Champion

@stchln 

 

Try this custom column as well

 

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

Regards
Zubair

Please try my custom visuals

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
Super User
Super User

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors