Nov 30 2020 09:58 AM - edited Nov 30 2020 12:33 PM
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)),
Nov 30 2020 11:51 AM
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
)
Nov 30 2020 12:26 PM
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.
Nov 30 2020 03:05 PM
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.