Combiner.CombineTextByDelimiter

Combiner.CombineTextByDelimiter is a Power Query M function that returns a function that combines a list of text into a single text using the specified delimiter.

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

Syntax

Combiner.CombineTextByDelimiter(
   delimiter as text,
   optional quoteStyle as nullable number,
) as function

Argument	Attribute	Description
Delimiter		A text value that specifies the separator to use when combining the text values.
Quotestyle	Optional	Determines how the function handles quotations in the text values. You can choose between QuoteStyle.None or QuoteStyle.Csv.

Description

The Combiner.CombineTextByDelimiter function returns a function that merges a list of text values into a single string, using a specified delimiter. This function is useful when you need to concatenate text values in a list with a specific separator.

The function takes two parameters: a delimiter (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 a Semicolon Delimiter

If you want to combine a list of text values using a semicolon as the delimiter, you would first define the parameters of the Combiner.CombineTextByDelimiter function like this:

Combiner.CombineTextByDelimiter( ";" )

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

// Output: "Apple;Grape;Orange"
Combiner.CombineTextByDelimiter( ";" )( {"Apple", "Grape", "Orange"} )

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: "Apple;Grape;Orange"
let 
   MyFunction    = Combiner.CombineTextByDelimiter( ", " ),   // returns a function
   ApplyFunction = MyFunction( {"Apple", "Grape", "Orange"} ) // 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.

Handling Quotations

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

// Output: "This is a "string in quotes""
Combiner.CombineTextByDelimiter( " ", QuoteStyle.None )(
  {"This is","a ""string in quotes"""} 
)

// Output: ""This is" "a ""string in quotes""""
Combiner.CombineTextByDelimiter( " ", QuoteStyle.Csv)(
  {"This is","a ""string in quotes"""} 
)

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.

Used by

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

Other functions related to Combiner.CombineTextByDelimiter are:

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