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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

AntrikshSharma 

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?

 

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
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

 

 

 

  • daveseibert's avatar
    daveseibert
    New Member
    2 years ago

    Hi All,

     

    This is much easier:

    List.RemoveFirstN(List.Transform(Text.Split(YOUR_TEXT_VARIABLE, "START_DELIM"), each Text.BeforeDelimiter(_, "END_DELIM")), 1)

     

     

    • 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 😁

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    ronrsnfld Thank you so much! This worked perfectly!

  • JustSayJoe's avatar
    JustSayJoe
    Icon for Advocate IV rankAdvocate IV
    2 years ago

    How do we accomplish this from a calculated table column?

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      2 years ago

      That's a different question. Suggest you post it as such so as to attract some DAX experts.