Text.Combine

Updated on

Text.Combine is a Power Query M function that combines a list of text values into a single text value. The function returns the combined text value, with an optional separator that can be specified for use in the final combined text.

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

Syntax

Text.Combine(
   texts as list,
   optional separator as nullable text,
) as text
ArgumentAttributeDescription
TextsText values provided within a list.
SeparatorOptional Provide separator as text value.

Description

Returns the result of combining the list of text values, texts, into a single text value. The function requires a list of text values (referred to as ‘texts’) to be combined. It also offers the option to define a ‘separator’, which, if provided, is inserted between each text value in the final output. This ‘separator’ can also be a null value, as denoted by ‘nullable’ in the syntax.

Examples

To better understand how the Text.Combine function works, let’s dive into a few illustrative examples.

Consider two text values: “Riverdale” and “12345”. We want to merge these into a single string. Here’s how you would use the Text.Combine function:

Text.Combine( {"Riverdale", "12345"} ) // Returns "Riverdale12345"

In this instance, the function merges “Riverdale” and “12345” to create “Riverdale12345”.

However, Text.Combine also allows you to define a separator for the combined text. For instance, if you want to combine a list of brand names, each separated by a comma and a space, you could do this:

// Output: "Vyro, Zynk, Kixx, Cygn, Nylo"
Text.Combine( {  "Vyro", "Zynk", "Kixx", "Cygn", "Nylo" }, ", " )

This would return “Vyro, Zynk, Kixx, Cygn, Nylo”, a single string with each brand separated by a comma and a space. Another benefit is that you can replace the above List of text values, with a reference to a table column.

Compare this to the manual method:

"Vyro" & ", " & "Zynk" & ", " & "Kixx" & ", " & "Cygn" & ", " & "Nylo"

It is more verbose to write long lists. And on top of that, with the manual method you can’t easily incorporate the values from a table column.

One unique aspect of Text.Combine is its handling of null values. Unlike the manual method of combining text values with operators, Text.Combine elegantly ignores any null values it encounters:

Text.Combine(  {"a", null, "c", null, "e" }, ", " ) // Output: "a, c, e"
Text.Combine combining text and null values

This is contrary to the manual method where the entire output becomes null when a null value is encountered in the combination:

"a" & null & "c" & null & "e" // Output: null
Manually combining Text Values and null

To conclude, the Text.Combine function in Power Query M language offers an effective way to merge text values, even when they include null values.

Learn more about Text.Combine in the following articles:

Other functions related to Text.Combine are:

BI Gorilla Youtube Channel

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