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

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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))/100

 

Whole 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