Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

Comparers, Combiners, Replacers and Splitters in Power Query

At the end of the Power Query Formula Library Specification (which can be downloaded here) are sections on Comparer, Combiner, Replacer and Splitter functions. These functions are most often used in conjunction with other functions like Table.CombineColumns() and Table.SplitColumn, but what you may not realise from the documentation (which also has a few minor but nonetheless confusing bugs in it) is what these functions do: they are functions that return functions, and the functions that they return can be used independently just like any other function.

Take Splitter.SplitTextByDelimiter() as an example. It returns a function that splits a piece of text by a delimiter, and returns a list containing the resulting pieces. The following M code calls this function to return a function that splits comma delimited text:

let

demo = Splitter.SplitTextByDelimiter(",")

in

demo

As noted here, once you have a query that returns a function you can see that function’s signature and invoke it from the Query Editor window. Here’s what the query above shows in the Query Editor window:

If you click the Invoke button and enter the text

one,two,three,four

As follows:

So that the code for the query becomes:

let

demo = Splitter.SplitTextByDelimiter(","),

Invokeddemo = demo("one,two,three,four")

in

Invokeddemo

What is returned is the list {“one”, “two”, “three”, “four”} which looks like this in the Query Editor window:

There are various other Splitter functions that can be used to return functions that split text in different ways. Similarly, the Combiner functions return functions that can be used to combine a list of text into a single piece of text. For example:

let

demo = Combiner.CombineTextByDelimiter("--"),

Invokeddemo = demo({"one","two","three","four"})

in

Invokeddemo

Returns the text

one–two–three—four

The Replacer functions return functions for replacing values in text , while the Comparer functions return functions that can be used for comparing text using specific cultures and case sensitivities.

OK, I’ve looked at this again and the main problem (as seen in the May 2014 version of the library spec pdf) is that a lot of the function signatures are wrong in the section on Combiners and Splitters. For example Combiner.CombineTextByDelimiter has the following incorrect signature in the docs:
Combiner.CombineTextByDelimiter(delimiters as list, optional quoteStyle as nullable number) as
function
In actual fact the first parameter is text, not a list.

Also, Splitter.SplitTextByEachDelimiter has the signature
Splitter.SplitTextByEachDelimiter(delimiters as list, optional quoteStyle as nullable number) as
function
…which is missing the optional third parameter.

[…] ConcatByComma, is a simple combiner that concatenates all of the text in a list using commas (see here for more details on how to do this). The second, MyNameFunction, is the important one – it uses […]

[…] ConcatByComma, is a simple combiner that concatenates all of the text in a list using commas (see here for more details on how to do this). The second, MyNameFunction, is the important one – it uses […]

Follow Blog via Email

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 7,729 other followers

Public Power BI and SSAS Training Courses

I'm running several SSAS, MDX and Power BI-related training courses through Technitrain in 2017. Check out the Technitrain course catalogue for full details, and to see other upcoming courses from the likes of Alberto Ferrari, Andy Leonard, Allan Hirt and Alex Yates.

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk