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
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
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.