Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Comparers, Combiners, Replacers and Splitters in Power Query

with 10 comments

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:

image

If you click the Invoke button and enter the text

one,two,three,four

As follows:

image

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:

image

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.

Written by Chris Webb

August 11, 2014 at 9:30 am

Posted in Power Query

10 Responses

Subscribe to comments with RSS.

  1. Lovely lambda expressions – but can you create new ones in “M”? I imagine “yes” but I’ve never seen an example.

    Marco Russo

    August 11, 2014 at 9:55 am

  2. Hi Chris. You mentioned a few confusing bugs in the documentation Comparer, Combiner, Replacer and Splitter functions. What issues are you seeing?

    Carla Sabotta
    SQL & BI Documentation

    Carla Sabotta

    August 12, 2014 at 9:44 pm

    • Hi Carla,

      There are a few typos in the PDF library document. I’m on vacation right now but when I get home I’ll send you the details.

      Chris Webb

      August 12, 2014 at 9:46 pm

      • Could you please send those typos to us (the PQ team)? The easiest way to get the info to us is to open Excel, go to the Power Query tab and then hit the Send Feedback button. Thanks!

        Ben Martens

        August 20, 2014 at 11:11 pm

      • I will – I’m still on vacation though!

        Chris Webb

        August 21, 2014 at 7:39 am

  3. Thanks Chris

    Carla Sabotta

    August 21, 2014 at 7:58 pm

    • 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.

      Chris Webb

      September 1, 2014 at 10:23 am

  4. […] 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 […]

  5. […] 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 […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: