Forum Discussion

JoakimHGjerde's avatar
JoakimHGjerde
Copper Contributor
Nov 30, 2020

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

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...
BI & Data Analysis
power bi
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 30, 2020

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
)
  • JoakimHGjerde's avatar
    JoakimHGjerde
    Copper Contributor
    Nov 30, 2020

    SergeiBaklan 

     

    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.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 30, 2020

      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

      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.

Resources