Text.Combine

Updated on
Rick de Groot

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.

Basic Example: Merging Text Values

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

Example with a Separator

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

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

This returns “Vyro, Zynk, Kixx, Cygn, Nylo”, a single string with each brand separated by a comma and a space. One advantage is that you can replace the above list of text values with a reference to a table column.

Manual Method Comparison

Compare this to the manual method of combining text:

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

The manual method is more verbose, especially for long lists. Additionally, you cannot easily incorporate values from a table column using this method.

Handling Null Values

One unique aspect of Text.Combine is its handling of null values. Unlike the manual method of combining text values with operators, Text.Combine 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 in contrast to the manual method, where the entire output becomes null if a null value is encountered in the combination:

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

The Text.Combine function in Power Query M language offers an effective way to merge text values, even when they include null values. Its ability to handle separators and null values efficiently makes it a useful tool for dealing with values.

Learn more about Text.Combine in the following articles:

Other functions related to Text.Combine are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy