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| Argument | Attribute | Description |
|---|---|---|
| Texts | Text values provided within a list. | |
| Separator | Optional | 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"
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
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.
Related articles
Learn more about Text.Combine in the following articles:
- Concatenate Values in Power Query
Learn how to Concatenate Values with Power Query. Find out how to handle delimiters, data types, and conditions like a pro. » Read more - Pivot Text Values in Power Query – Explained
What is the Pivot Columns operation in Power Query? And how can you use it to pivot text values and numbers? Follow along to find out! » Read more - Replace Values in Power Query M (Ultimate Guide)
Learn how to replace values in Power Query. Look into conditional replacements, replacing in multiple columns, case sensitivity and more! » Read more - Concatenate Text with Group By (Combine Rows) in Power Query
In this post you learn how to use group by to concatenate text values. It allows you to summarize the data in a comma separated list. » Read more - Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more
Related functions
Other functions related to Text.Combine are:
