How to Concatenate Multiple Columns in Power BI

The easiest way to concatenate more than 2 columns together in Power BI is to use the & symbol in DAX. This approach provides maximum control over the placement and type of separators used between the concatenated elements.

For instance, if you have three separate columns containing components of a name and you wish to combine them into a single Full Name column, you can use the following concise and highly readable syntax:

Full Name = 'my_data'[First] & " " & 'my_data'[Middle] & " " & 'my_data'[Last]

This particular example creates a new column named Full Name that concatenates the strings from the First, Middle and Last columns. Crucially, it uses the literal string ” “ (a single space enclosed in double quotes) as the separator between the individual string components. This explicit definition of the separator ensures that the resulting merged data is clean, formatted, and easily readable for end-users.

The following section provides a detailed, step-by-step example demonstrating how to implement this syntax within the Power BI desktop environment, transforming raw, separated data into a unified field.

Step-by-Step Example: Implementing the Calculated Column

To illustrate the application of the & operator for multi-column concatenation, let us consider a practical scenario. Suppose we are working with a table named my_data that contains granular details for personnel records, including the distinct columns for the first, middle, and last names. Our goal is to derive a single column that presents the complete name, properly formatted with spaces, without altering the source columns.

Suppose we have the following table named my_data in Power BI, which represents a typical structure encountered during data import and preparation:

Our immediate objective is to create a new derived column that successfully concatenates the strings from the First, Middle, and Last columns. This transformation requires navigating the Power BI interface to initiate the creation of a new calculated column and then entering the required DAX formula. This process ensures that the new column is an integral part of the data model and responds dynamically to changes in the source data.

To begin the process of creating this calculated field, you must first ensure you are focused on the correct table in the Data view or Report view. Then, click the Table tools tab located in the ribbon menu at the top of the Power BI Desktop application. Within this tab, locate and click the New column icon. This action immediately generates a formula bar, awaiting your DAX expression, and sets up the necessary Row Context for the calculation.

Practical Application: Creating the ‘Full Name’ Column

Once the formula bar is visible after clicking the New column button, we can input the specific DAX expression designed for multi-column concatenation. The structure of the formula must meticulously reference the table name, the column names (enclosed in square brackets), and the desired separators (enclosed in double quotes), all linked by the ampersand operator.

Then type the following formula into the formula bar. Note the strict sequence: column reference, ampersand, separator string, ampersand, next column reference, and so on. This formula precisely defines how the three separate text fields will be merged:

Full Name = 'my_data'[First] & " " & 'my_data'[Middle] & " " & 'my_data'[Last] 

Upon confirming the formula (by pressing Enter or clicking the checkmark), Power BI calculates the resulting string for every row in the my_data table. This creates a new column named Full Name that successfully concatenates the strings from the First, Middle and Last columns, using spaces as the designated separator between the strings. The immediate visual result confirms the success of the operation, providing a clean, single field ready for use in reports.

Customizing Delimiters and Handling Separators

A key advantage of using the & operator in DAX for concatenation is the ability to fully customize the delimiter used to separate the fields. While a space is often appropriate for names, many data requirements necessitate different separators, such as a comma and space (e.g., for address formatting), a hyphen, or a pipe symbol (e.g., for creating unique key identifiers).

It is important to note that we chose to use a simple space as a separator in the previous example, but you could use any separator you’d like by explicitly defining the text string after the & symbols. The choice of separator should be governed by the intended use of the concatenated column. If the column is meant purely for display, readability is paramount. If, however, the column is intended to be used as a key or needs to be parsed by another system later, a non-standard character like a pipe (|) might be preferable, as it is less likely to appear within the actual data itself.

For example, suppose the requirement shifts, and the middle name should be separated from the first and last names by a comma, perhaps to align with specific academic or administrative formatting standards. You could easily modify the formula to replace the space delimiter ” “ with a comma and space “, “, or just a comma, as shown below:

Full Name = 'my_data'[First] & "," & 'my_data'[Middle] & "," & 'my_data'[Last] 

Executing this adjusted formula results in a change in how the values are presented in the new column, demonstrating the flexibility of the DAX concatenation operator. This will concatenate the values from the three columns with a comma placed between each string, providing a different textual presentation of the data:

Scaling Concatenation: Beyond Three Columns

The principles demonstrated for three columns extend seamlessly to any arbitrary number of columns, making the & operator highly scalable for fixed concatenation tasks. If you needed to combine six different columns—perhaps a complex product code consisting of category, size, color, year, region, and ID—you would simply continue the pattern: column reference followed by ampersand, followed by the delimiter string, and so on, until all required fields are included.

Note: In this example, we concatenated three columns together, but you can use the & symbol as many times as you’d like to concatenate even more columns together. The primary constraint, in this case, is not the DAX functionality, but rather the complexity and manageability of the formula itself. For very long concatenation chains (e.g., more than eight or ten components), the formula can become lengthy and prone to manual error, which might prompt consideration of alternative iterative functions.

A crucial consideration when dealing with multi-column concatenation is handling potential blank or null values in the source columns. If the middle name column, for instance, is blank for certain records, the formula `’my_data'[First] & ” ” & ‘my_data'[Middle] & ” ” & ‘my_data'[Last]` would result in an extra space appearing where the middle name should be (e.g., “John Smith”). To prevent this, advanced DAX logic involving functions like `IF` and `ISBLANK` or `COALESCE` is often integrated into the calculation to conditionally include the delimiter only if the intermediate column has a value. This attention to detail ensures the resulting concatenated string is perfectly formatted regardless of data completeness.

Alternative Approach: Leveraging CONCATENATEX for Iterative Merging

While the & operator is ideal for fixed, row-by-row merging of individual columns, situations may arise where you need to concatenate multiple strings across different rows within a group (e.g., listing all cities visited by a customer). For these scenarios, the CONCATENATEX function provides a more powerful, iterative solution.

DAX’s CONCATENATEX is an iterator function that evaluates a string expression for every row of a specified table and then combines the resulting strings into a single text value, using a defined delimiter. This function is typically used in measures rather than calculated columns and excels when summarizing text data based on relationships in your data model. For example, you could use CONCATENATEX to create a single field on a Customer table that lists all product categories that customer has purchased, separated by commas.

The key difference is context. The & operator works purely within the Row Context to merge fixed columns for that single record. CONCATENATEX works within a Filter Context, allowing it to aggregate strings from related rows into a single cell, thereby offering a solution for complex grouping and summary tasks that simple concatenation cannot address. Although overkill for merging three simple name columns, understanding CONCATENATEX provides a comprehensive toolset for all types of text merging challenges in Power BI.

Summary of Best Practices for Column Concatenation

Successfully implementing multi-column concatenation enhances data clarity and simplifies reporting in Power BI. To ensure accuracy and maintainability, adhere to the following best practices:

1. Always Use Explicit Delimiters: Never rely on natural spacing; always insert a literal string (e.g., " ", ", ", or "|") using the & operator to maintain readability and structure in the resulting concatenated column. The choice of delimiter should be consistent across similar data types.

2. Proactively Handle Missing Data: Implement DAX logic (such as nested IF statements checking ISBLANK or utilizing specialized functions) to suppress delimiters or entire segments of the concatenation formula if the source column value is null or empty. This prevents unsightly double separators (e.g., "Name,,ID") in your final output.

3. Use Descriptive Column Names: Name the new calculated column clearly (e.g., “Full_Address,” “Composite_Key,” or “Formatted_Name”) to differentiate it from the original granular source columns. This practice is crucial for data governance and clarity when other report developers interact with the data model.

By mastering the use of the & operator for fixed concatenation and understanding the iterative power of CONCATENATEX, Power BI users can efficiently transform raw data into optimized structures, significantly improving the analytical and visualization capabilities of their reports.

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