Forum Discussion
Extract Multiple Occurrences of Text Between Delimiters
- 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
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)This does seem much simplier. One issue is it captures the first value infront of the start delimiter. Any suggestions?
Goal: Capture all numeric values, sum and convert to decimal (not present in code yet)
Code:
Table.AddColumn(#"Removed Other Columns1", "Custom", each List.Transform(Text.Split([#"FTE`s required"], "["), each Text.BeforeDelimiter(_, "%")))
Field Value:AUTO1[100%],MOT1[100%],MOT2[25%],OPS1[25%] Desired Result:
{100,100,25,25}
Current Result
{Auto1,100,100,25,25}
End state desired result - Attempting to use List.Sum with additional LEN logic to insert decimal but can't get there until text value is removed.
2.50
ronrsnfld Thank you so much! This worked perfectly!
JustSayJoeAdvocate IV
How do we accomplish this from a calculated table column?
- ronrsnfld
That's a different question. Suggest you post it as such so as to attract some DAX experts.
- List.Accumulate to create each delimited substring