How to Combine Columns in Power BI Using Append Queries

Leveraging DAX for Column Combination

Data Analysis Expressions, or DAX, provides a powerful functional language used to create new information from data already in your model. When concatenating columns, DAX offers specialized functions designed specifically for string manipulation. The primary function employed for this purpose is CONCATENATE, which is versatile but often limited to combining exactly two string arguments. For combining more than two columns, or for complex operations involving separators and multiple data types, the ampersand operator (&) offers far greater flexibility and control over the resulting string structure.

Using DAX allows the calculation to be performed on the fly based on the relationships and filters applied in the report context, which is a major advantage over static transformations performed in Power Query. When you define a new calculated column using DAX, the calculation is executed row-by-row across the table, ensuring that the concatenated result is accurate for every record. It is crucial to remember that the columns being concatenated must be of a string format, or easily convertible to strings, as concatenation is a fundamental text operation.

We will now explore two fundamental formulas using DAX to achieve column concatenation. These examples demonstrate the core methods: a simple, unseparated join using the dedicated function, and a more flexible join utilizing the concatenation operator (&) alongside text literals to introduce necessary spacing or punctuation. Mastering these two approaches is essential for any Power BI developer seeking to enrich their data model.

Formula 1: Concatenate Two Columns with No Separator

New Column = CONCATENATE('my_data'[Column 1], 'my_data'[Column 2])

This particular formula utilizes the strict CONCATENATE function, which is designed to accept exactly two arguments. It effectively concatenates the strings found in Column 1 and Column 2 together with no separator between them, resulting in a single continuous text string.

Formula 2: Concatenate Two Columns with Separator

New Column = CONCATENATE('my_data'[Column 1] & " ", 'my_data'[Column 2])

This formula demonstrates a slightly more complex structure where the first argument to CONCATENATE is an expression. It uses the & symbol to join the value from Column 1 with a literal space character (" "), creating an intermediate string, which is then joined with the value from Column 2. This method is preferred when clarity requires proper spacing or punctuation.

The following examples show how to use each formula in practice with the sample table named my_data in Power BI, which contains separate columns for First and Last names:

Example 1: Concatenate Two Columns with No Separator

To concatenate the strings in the First and Last columns together with no separator, you must first define a new calculated column. Navigate to the Table tools tab in Power BI Desktop after selecting your table, and then click the New column icon. This action opens the Formula Bar where the DAX expression will be entered.

This approach is ideal for internal identifiers or merging codes where any intervening character is undesirable. Type the following formula into the Formula Bar, ensuring the table and column references match your data model exactly:

First and Last = CONCATENATE('my_data'[First], 'my_data'[Last]) 

This will create a new column named First and Last that concatenates the strings in the First and Last columns together with no separator. As illustrated below, the resulting names are merged immediately adjacent to one another, highlighting the function’s strict two-argument joining behavior:

Example 2: Concatenate Two Columns with Separator

When preparing data for reports, especially for full names, inserting a separator like a space is critical for readability. To concatenate the strings in the First and Last columns together with a specific separator, begin by clicking the Table tools tab, then click the New column icon. This opens the Formula Bar where we will leverage the ampersand operator for customization.

The formula below constructs the desired full name by first joining the First name with a space literal (" ") using the & operator, and then joining that intermediate result with the Last name value. This technique ensures the output adheres to standard naming conventions. Enter the following detailed formula into the Formula Bar:

First and Last = CONCATENATE('my_data'[First] & " ", 'my_data'[Last]) 

Note that we chose to use a space as a separator, but you could use any delimiter you’d like by specifying it after the & symbol within the double quotes. The flexibility provided by the ampersand operator extends far beyond simple spacing, allowing for complex formatting required by stringent data quality standards.

Advanced Custom Separators

The ability to define custom separators is a powerful aspect of DAX string manipulation. If, for instance, your data model requires a dash or underscore to separate key elements for internal referencing, you can easily substitute the space literal in the previous example with the required character. This guarantees standardization across various data transformation tasks.

For example, you could use the following syntax to use a dash as a separator, which is often used in structured identifiers:

First and Last = CONCATENATE('my_data'[First] & "-", 'my_data'[Last]) 

This will concatenate the values from the First and Last columns together with a dash in between them, providing a clear demonstration of how easily the separator can be adapted to various formatting needs:

Summary of Concatenation Methods

In summary, successfully concatenating two columns in Power BI is fundamentally achieved through the use of DAX calculated columns. While the dedicated CONCATENATE function works well for two arguments without complex formatting, the ampersand operator (&) is the preferred method for any scenario involving separators or the chaining of more than two text fields, as it provides the necessary control over the output structure.

The following tutorials explain how to perform other common tasks in Power BI: