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
Super 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
Hi 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
- AlienSx
List.Alternate( Splitter.SplitTextByAnyDelimiter({"[", "%]"})(value), 1, 1 )to get the list or even
let value = "AUTO1[100%],MOT1[100%],MOT2[25%],OPS1[25%]", summm = List.Sum( Record.FieldValues( Expression.Evaluate( "[" & Text.Combine( List.ReplaceMatchingItems( Text.ToList(value), {{"[", "="}, {"]", ""}, {"%", ""}} ), "" ) & "]" ) ) ) in summmto get sum of values 😁
- dufoq3
Community Champion
Hi fox252, different approach here:
Result
Add this code as custom column. Replace [Column1] with your column name.
List.Sum(List.Select(List.Transform(Text.SplitAny([Column1], "[%"), (x)=> try Number.From(x) otherwise false), (y)=> y <> false))/100Whole code with sample data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwN8TeMNjQwUI3V8fUPQWIaRRuZAln+AcGGYJZSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_Result = Table.AddColumn(Source, "Result", each List.Sum(List.Select(List.Transform(Text.SplitAny([Column1], "[%"), (x)=> try Number.From(x) otherwise false), (y)=> y <> false))/100, type number) in Ad_Result Hey, nearly there! Don't forget the List.RemoveFirstN at the start. That will get rid of the first element.
Thanks! I missed this function and was instead using List.LastN in combination with a List.Count - 1. Much cleaner now.
