Power Query Text.Combine: How to Use It

2 min read 23-10-2024

Power Query is a powerful tool in Microsoft Excel and Power BI that allows users to transform and prepare data for analysis. One of the most useful functions in Power Query is Text.Combine, which enables users to concatenate multiple text values into a single text value. In this blog post, we’ll explore how to use Text.Combine, its syntax, examples, and best practices.

What is Text.Combine? 🤔

Text.Combine is a function in Power Query that concatenates a list of text values into one string, optionally separated by a specified delimiter. This is particularly useful when you need to create a unified string from various text fields in your dataset.

Syntax of Text.Combine

The syntax for Text.Combine is straightforward:

Text.Combine(list as list, optional delimiter as nullable text) as text

list: This is the list of text values you want to combine.
delimiter: This is an optional parameter that specifies the character(s) to use as a separator between the combined text values. If not specified, no delimiter will be used.

Example of Text.Combine in Action 📊

Let’s consider a practical example. Imagine you have a list of first names and last names and you want to combine them into full names.

First Name	Last Name
John	Doe
Jane	Smith
Alice	Johnson

You can create a new column with full names using Text.Combine.

let
    Source = Table.FromRecords({
        [FirstName="John", LastName="Doe"],
        [FirstName="Jane", LastName="Smith"],
        [FirstName="Alice", LastName="Johnson"]
    }),
    AddFullName = Table.AddColumn(Source, "FullName", each Text.Combine({[FirstName], [LastName]}, " "))
in
    AddFullName

In the above example, the Text.Combine function concatenates the FirstName and LastName with a space as a delimiter, resulting in:

Full Name
John Doe
Jane Smith
Alice Johnson

Using Different Delimiters 🛠️

You can choose different delimiters based on your requirements. For instance, if you want to combine text values with a comma and a space, you can modify the Text.Combine function as follows:

Text.Combine({[FirstName], [LastName]}, ", ")

This would result in:

Full Name
John, Doe
Jane, Smith
Alice, Johnson

Important Notes ⚠️

Remember: If any of the text values in the list is null, Text.Combine will treat it as an empty string, and the resulting combined string will not include extra delimiters for the null values.

Best Practices for Using Text.Combine ✨

Choose the Right Delimiter: Always select a delimiter that makes sense for your dataset to ensure clarity when reading the combined text.
Handle Null Values: Be cautious of null values that might affect the final output. Consider replacing nulls with a default string if necessary.
Keep It Simple: Avoid creating overly complicated concatenations. Simplicity makes it easier for others to read and understand your data transformations.

Conclusion 🏁

Text.Combine is a versatile function that can greatly enhance your data manipulation capabilities in Power Query. By mastering this function, you’ll be able to create more meaningful and informative text outputs from your data. Whether you’re working with names, addresses, or other textual data, Text.Combine will streamline your data preparation processes, making your reports and analyses more effective. Happy data combining!

Power Query Text.Combine: How to Use It

Table of Contents :