Combiner.CombineTextByEachDelimiter

Combiner.CombineTextByEachDelimiter is a Power Query M function that returns a function that combines a list of text into a single text using each specified delimiter in sequence.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Combiner.CombineTextByEachDelimiter(
   delimiters as list,
   optional quoteStyle as nullable number,
) as function

Argument	Attribute	Description
Delimiter		A list of text values that act as separators when merging text values. These separators are used in the order they appear in the list. If there are more text values than separators, the remaining text values are combined without any separators.
Quotestyle	Optional	Determines how the function handles quotations in the text values. You can choose between QuoteStyle.None or QuoteStyle.Csv.

Description

The Combiner.CombineTextByEachDelimiter function returns a function that merges a list of text values into a single string, using each specified delimiter in sequence. This function is particularly useful when you need to concatenate text values in a list with different separators.

The function takes two parameters: a list of delimiters (required) and an optional quotestyle.

The combiner functions are a bit different from most functions in the Power Query M language because they return a function. This means that using it involves two steps.

You specify the function parameters: the delimiter and optionally the quote style.
You provide a list of text values that the function will combine based on the parameters of step 1.

Examples

Let’s look at some examples to illustrate this:

Combining Text Values with Multiple Delimiters

If you want to combine a list of text values by using a range of delimiters, you would first define the parameters of the Combiner.CombineTextByEachDelimiter function like this:

Combiner.CombineTextByEachDelimiter( {"1","2","3"} )

Then, to combine the text values “Apple”, “Grape”, and “Orange”, you would write:

// Output: "Apple1Grape2Orange3Guava"
Combiner.CombineTextByEachDelimiter( {"1","2","3"} )( {"Apple", "Grape", "Orange", "Guava"} )

This operation concatenates the list of text values using the delimiters in the specified order.

Using Variables for Clarity

Knowing how other functions work in the Power Query M language, this may be slightly confusing. To make things clearer you can use variables. Here’s how you can do it:

// Output: "Apple1Grape2Orange3Guava"
let 
   MyFunction    = Combiner.CombineTextByEachDelimiter( {"1","2","3"} ),   // returns a function
   ApplyFunction = MyFunction( {"Apple", "Grape", "Orange", "Guava"} ) // applies the function
 in 
   ApplyFunction

The line where we define ApplyFunction uses MyFunction as an actual function. Therefore it has opening and closing parenthesis following. What’s happening is identical to the earlier statement though.

Combining Text Values with Insufficient Delimiters

What happens when you want to combine more values than the number of delimiters provided? In such cases, Power Query will concatenate the extra values without any delimiter. Let’s see this in action:

// Output: "Apple1Grape2Orange3GuavaPear"
Combiner.CombineTextByEachDelimiter( {"1","2","3"} )( 
   {"Apple", "Grape", "Orange", "Guava", "Pear" } 
)

In this example, we used 3 delimiters to combine 5 values. The first three pairs of values are combined using the delimiters “1”, “2”, and “3”. However, since we ran out of delimiters, the remaining values “Guava” and “Pear” are concatenated without any delimiter.

This example highlights the flexibility of the Combiner.CombineTextByEachDelimiter function. It can handle scenarios where the number of text values exceeds the number of provided delimiters, ensuring that all your data is included in the final output.

Handling Quotations

You can optionally provide a QuoteStyle.Type for the text values. This impacts how quotations are treated. For example:

// Output: "I1love2my "food""
Combiner.CombineTextByEachDelimiter( {"1","2","3"}, QuoteStyle.None )(
  {"I","love", "my ""food"""} 
)

// Output: "I1love2"my ""food""""
Combiner.CombineTextByEachDelimiter( {"1","2","3"}, QuoteStyle.Csv)(
  {"I","love", "my ""food"""} 
)

In the first example, QuoteStyle.None is used, so the quotation marks in the text values are preserved. In the second example, QuoteStyle.Csv is used, which adds an extra set of quotation marks around each text value.

By mastering the Combiner.CombineTextByEachDelimiter function, you can efficiently manipulate and combine text values in Power Query M language. This function is a powerful tool in your data transformation toolkit, especially when working with text data that needs to be combined in a specific way.

Used by

While you can use the Combiner.CombineTextByEachDelimiter function by itself, it also works together with:

Other functions related to Combiner.CombineTextByEachDelimiter are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/combiner-combinetextbyeachdelimiter