Forum Discussion
Extract Multiple Occurrences of Text Between Delimiters
I have one column of text that has various keywords or phrases that are separated by "~~" before and after each word/phrase. I need to exact the text that is contained between this custom delimiter every time that it occurs (Note: Text.BetweenDelimiters only extracts the text from the 1st occurrence instead of extracting it from each occurrence).
I need to concatenate each keyword/phrase that was extracted and separate them with a ";" delimiter.
The below image shows an example of the text input and the expected output that I need.
How can I accomplish this in Power Query?
- List.Accumulate to create each delimited substring
- Count the number of delimiters
- Create a list and extract every other number (the start index of each delimiter pair
- Note that my data comes from an Excel table. Change the Source= line to reflect your actual data source.
let Source = Excel.CurrentWorkbook(){[Name="Table26"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), Delim = "~~", Extract = Table.AddColumn(#"Changed Type", "Text Between ~~", each Text.Combine( List.Accumulate( List.Alternate(List.Numbers(0, List.Count(Text.Split([Column1], "~~"))-1),1,1,1), {}, (state, current)=> state & {Text.BetweenDelimiters([Column1],Delim,Delim,current)}),"; "), type text) in Extract- List.Accumulate to create each delimited substring
16 Replies
- ImkeF
Community Champion
Hi Anonymous ,
I've created a function for that a while ago: Return all occurrences of text between delimiters in Power BI and Power Query – The BIccountant
Please let me know if that works for you. Hi ImkeF , thanks for your help. I did actually see this post previously but I wasn't able to figure out where or how to convert your text explanations into something that works using my own values. I saw that you have a .pbix file linked but I am only running power query in excel on a Mac so I am not able to open the file.
Do you think you'd be able to copy and paste your code from your file here so I can see how this works from a functioning example?
- AntrikshSharma
Anonymous Basesd on the sample shared you can use this:
let Source = Table.FromRows ( Json.Document ( Binary.Decompress ( Binary.FromText ( "i45WCsnILFaoqytOzStJzUtOratTAPKzSotLFBLzgOKpFYm5BTkg4fw0hUSFktSKEoXMvILSEj2l2FgA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ( ( type nullable text ) meta [ Serialized.Text = true ] ) in type table [ Text = _t ] ), AddedCustom = Table.AddColumn ( Source, "Output", each Text.Combine ( List.RemoveItems ( List.Transform ( Text.Split ( [Text], " " ), each Text.BetweenDelimiters ( _, "~~", "~~" ) ), { "" } ), "; " ), type text ) in AddedCustomThis is very close but I am not only extracting single words every time. I am also extracting whole phrases so I think splitting the text at each space is causing errors for me.
Here is a new example that shows how both words and phrases need to be extracted. How can we change your code to fix this?
ronrsnfldSuper User
- List.Accumulate to create each delimited substring
- Count the number of delimiters
- Create a list and extract every other number (the start index of each delimiter pair
- Note that my data comes from an Excel table. Change the Source= line to reflect your actual data source.
let Source = Excel.CurrentWorkbook(){[Name="Table26"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), Delim = "~~", Extract = Table.AddColumn(#"Changed Type", "Text Between ~~", each Text.Combine( List.Accumulate( List.Alternate(List.Numbers(0, List.Count(Text.Split([Column1], "~~"))-1),1,1,1), {}, (state, current)=> state & {Text.BetweenDelimiters([Column1],Delim,Delim,current)}),"; "), type text) in Extract- List.Accumulate to create each delimited substring
