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 e...
- 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
AntrikshSharma
Community Champion
Community ChampionAnonymous 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
AddedCustom
This 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 ExtractHi All,
This is much easier:
List.RemoveFirstN(List.Transform(Text.Split(YOUR_TEXT_VARIABLE, "START_DELIM"), each Text.BeforeDelimiter(_, "END_DELIM")), 1)
- List.Accumulate to create each delimited substring