Today I’ll present an adjustment to the Text.SplitAny – function in Power BI’s query editor or Power Query. The native function takes a string as an input and splits the text by every character that is contained in the string. This seems fairly unusual to me and I haven’t used that function very often.
Problem
But what I have come across fairly often is the requirement to split a string by a bunch of different (whole) strings (instead of single characters).
Solution
Therefore I’ve modified the native Text.SplitAny – function so that it also accepts lists with strings as its second parameter:
How it works
This new function checks if the 2nd parameter is of type text and if so, applies the native function. But if it’s not text but a list with strings, it applies a different transformation instead, that:
- Walks through the list of separators, using the List.Accumulate-function (row 13)
- Splits the text by every separator in that list (row 17).
- But as the result of a splitted text is a list, this operation has to be performed on every item of that list. Hence I’m using List.Transform for it. List.Transform also walks through every item of a list and does a transformation on it, like List.Accumulate. But it doesn’t feed it’s results of every iteration step to the subsequent step: Instead it simply stores each transformed list element on its original position and returns it there at the end. So while List.Accumulate can return a result of any format (depending on the function you perform in it), List.Transform will always return a list of the same length than the one you feed it.
- Flatten the nested list before the next iteration (row 18).
- When you successfully split each text-item of a list (into a list), you end up with a nested list. But this is not the ideal shape for further transformations. So I flatten the nested list into a simple list for the next iteration-round.
- Gets rid of any empty fields that might have been created by the splitting operation above (row 19)
Hope you enjoy & stay queryious đ
Hi,
Will the query below achieve the same result as your function or is there a situation where your function achieves a different result? I can’t seem to find a case where the outcomes aren’t the same. I’m curious because I’ve read List.Accumulate isn’t the most efficient function and should be avoided when possible.
let
fnTextSplitAny = (string as text, separator as any) =>
let
TypeListSplit =
let
fnSplit = Splitter.SplitTextByAnyDelimiter(separator),
InvokeSplit = fnSplit(string),
RemoveBlanks = List.RemoveItems(InvokeSplit, {“”, ” “})
in
RemoveBlanks,
TypeTextSplit = Text.SplitAny(string, separator),SplitCondition =
if Value.Is(separator, type list) = false
then TypeTextSplit
else TypeListSplit
in
SplitCondition
in
fnTextSplitAny
Thank you Jason,
your code is looking good – thanks for sharing!
/Imke