Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago
    • 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

     

     

     

fox252's avatar
fox252
Frequent Visitor
2 years ago

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's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago
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
    summm

to get sum of values 😁