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 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)),

BI & Data Analysis
power bi

3 Replies

  • 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