PowerQuery - Text.BetweenDelimiters with StartDelimiter from list (ie. multiple)

Copper Contributor

Hi all, 

I need to extract Text from a Column in to a seperate column, but where they StartDelimiter can consist of 3-4 different options due to differences in language. Is it possible to use a List for this purpose? Have tried to replicate a code used for List.AnyTrue but no luck. 

 

The list is called Currency_Conversion_Delimiter and current code is as follows. No syntax error but doesnt work as expected (ie. if I change out the StartDelimiter with the only value in the list it works, but not via the below).

 

    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Added Conditional Column", "Text Between Delimiters", each List.Transform (Currency_Conversion_Delimiter, (StartDelimiter) => Text.BetweenDelimiters([Description], StartDelimiter, " "), type text)),

3 Replies

@JoakimHGjerde 

Not sure I understood you correctly, but if split column on few ones using list of delimiters, when

=Table.SplitColumn(
   prevStep,
   "Column",
   Splitter.SplitTextByAnyDelimiter({"del1","del2","del3"}, QuoteStyle.Csv),
   null
)

@Sergei Baklan 

 

Hi Sergei, 
Apologize, it is actually an extract I am trying to do (ie. extract the content between the delimiters). 

 

The StopDelimiter will always be the same, but the StartDelimiter may as an example be "belop" or "belob" due to the language differences. As such, I would like to create a list with these required delimiters, and use them as StartDelimiter in Text.BetweenDelimiters if possible, as the Delimiter will only ever occour once.

@JoakimHGjerde 

That's better with small sample, but let me try as I understood you. As a basis I'd take slightly modified function suggested by Ingeborg here Improved Text.SplitAny - function for Power BI and Power Query – The BIccountant

(string as text, separator as any) =>

let

    ListFunction = List.Accumulate(
        separator, 
        {string}, 
        (state, current) => 
        [   DoForEveryItemInTheList = List.Transform(state, each Text.Split(_, current)),
            FlattenNestedList = List.Combine(DoForEveryItemInTheList),
            RemoveEmpties = List.Select(FlattenNestedList, each _<>"" and _<>" ")
        ][RemoveEmpties]
    )
in
    ListFunction

Assuming any of "first" delimiters is always paired with "second" delimiter and end of the text we consider as "second" delimiter, transition from left to right here

image.png

could be done as

let
    firstDelims = {"ABC", "XYZ"},
    secondDelim = "/>",
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(
        Source,
        "Custom",
        each
            List.Transform(
                List.FindText(
                    fnSplitText([A] & secondDelim,firstDelims),
                    secondDelim
                ),
                each
                    Text.Start(_, Text.PositionOf(_,secondDelim) )
            )
    ),
    NumberOfDelims = Table.AddColumn(
        #"Added Custom",
        "CountDelims",
        each List.Count([Custom])
    ),
    maxNumberOfDelims = List.Max(NumberOfDelims[CountDelims]),
    ColNames = List.Transform({1..maxNumberOfDelims}, each "Col " & Text.From(_) ),
    #"Extracted Values" = Table.TransformColumns(
        #"Added Custom",
        {"Custom",
         each
            Text.Combine(
                List.Transform(_, Text.From),
                ";"
            ),
         type text
        }
    ),
    #"Split Column by Delimiter" = Table.SplitColumn(
        #"Extracted Values",
        "Custom",
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
        ColNames),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"A"})
in
    #"Removed Columns"

This module also could be converted to function.