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

fox252's avatar
fox252
Frequent Visitor
2 years ago

Thanks to everyone for their help. Starting to fully appreciate how many different approaches there are to such problems. I love Power Query.

 

Final code I came up with below. Hope this helps someone in the furture! dufoq3  has a ingenious approach as well.

 

if [#"FTE`s required"] = null
then 0
else
(List.Sum(List.Transform(List.RemoveFirstN(List.Transform(Text.Split([#"FTE`s required"], "["), each Text.BeforeDelimiter(_, "%")),1), each Number.FromText(_))))/100)

 

Field Value: 

AUTO1[100%],MOT1[100%],MOT2[25%],OPS1[25%]

 

Result

2.5

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

    You're welcome 😉