Extract Multiple Occurrences of Text Between Delimiters

[adamcoffey15]

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 every time that it occurs (Note: Text.BetweenDelimiters only extracts the text from the 1st occurrence instead of extracting it from each occurrence). 

I need to concatenate each keyword/phrase that was extracted and separate them with a ";" delimiter. 

The below image shows an example of the text input and the expected output that I need.

How can I accomplish this in Power Query?

[AntrikshSharma]

@adamcoffey15 Basesd on the sample shared you can use this:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WCsnILFaoqytOzStJzUtOratTAPKzSotLFBLzgOKpFYm5BTkg4fw0hUSFktSKEoXMvILSEj2l2FgA",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Text = _t ]
    ),
    AddedCustom = 
        Table.AddColumn (
            Source,
            "Output",
            each Text.Combine (
                List.RemoveItems (
                    List.Transform (
                        Text.Split ( [Text], " " ),
                        each Text.BetweenDelimiters ( _, "~~", "~~" )
                    ),
                    { "" }
                ),
                "; "
            ),
            type text
        )
in
    AddedCustom

[adamcoffey15]

@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 · Accepted solution]

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

Hi All,

This is much easier:

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

[fox252]

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

[davidseibert]

Hey, nearly there! Don't forget the List.RemoveFirstN at the start. That will get rid of the first element.

[JustSayJoe]

How do we accomplish this from a calculated table column?

[ronrsnfld]

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

[adamcoffey15]

@ronrsnfld Thank you so much! This worked perfectly!

[adamcoffey15]

Hi @ImkeF , thanks for your help. I did actually see this post previously but I wasn't able to figure out where or how to convert your text explanations into something that works using my own values. I saw that you have a .pbix file linked but I am only running power query in excel on a Mac so I am not able to open the file. 

Do you think you'd be able to copy and paste your code from your file here so I can see how this works from a functioning example?

[ImkeF]

Hi @adamcoffey15 ,
I've created a function for that a while ago: Return all occurrences of text between delimiters in Power BI and Power Query – The BIccountant
Please let me know if that works for you.