How to combine different columns in Power BI?

by

As a Data analyst, you often need to bring together cell values from entire columns or part of them. This tutorial will guide you through the process of concatenating multiple columns in Power BI, using a practical HR scenario.

In this tutorial we will assume a single table named “Employees” with the following columns: EmployeeID (whole number), FirstName (text), LastName (text), Department (text), and Position (text).

Concatenate cell values with DAX

To concatenate multiple columns in Power BI using DAX:

  1. Open your report in Power BI Desktop.
  2. Click on “New measure” in the Home tab.
  3. In the formula bar, enter the following DAX formula:
Employee Profile = 
CONCATENATEX(
    Employees,
    Employees[FirstName] & " " & Employees[LastName] & " - " & 
    Employees[Department] & ", " & Employees[Position],
    ", "
)
  1. Click the checkmark or press Enter to create the measure.
  2. Drag the new “Employee Profile” measure into a table or card visual.

This formula uses CONCATENATEX to iterate through the Employees table, combining FirstName, LastName, Department, and Position for each employee. The result is a comma-separated list of employee profiles.

To use this in a visual:

  1. Create a new Table visual.
  2. Drag EmployeeID to the visual’s field well.
  3. Add the “Employee Profile” measure to the visual.

You now have a table showing each employee’s ID alongside their comprehensive profile.

For a more dynamic approach, create a calculated column instead:

  1. In the Data view, select the Employees table.
  2. Click “New Column” in the Table tools tab.
  3. Enter the following DAX formula:
Employee Profile Column = 
Employees[FirstName] & " " & Employees[LastName] & " - " & 
Employees[Department] & ", " & Employees[Position]
  1. Use this new column in your visuals for individual employee profiles.

Combine cell values with Power Query

A more efficient method is to use Power Query to concatenate columns, especially for large datasets or when you need to perform the concatenation during the data loading process:

  1. In Power BI Desktop, go to Home > Transform data to open Power Query Editor.
  2. Select the Employees table in the Queries pane.
  3. Go to Add Column > Custom Column.
  4. In the “New column name” field, enter “Employee Profile”.
  5. In the “Custom column formula” field, enter:
[FirstName] & " " & [LastName] & " - " & [Department] & ", " & [Position]
  1. Click OK to create the new column.
  2. If needed, you can modify the data type of the new column to Text.
  3. Close & Apply to save your changes and return to Power BI Desktop.
  4. Here’s our new column:

This method creates the concatenated column directly in the query, which can be more performant for large datasets as it’s calculated only once during data refresh rather than on-demand like DAX measures.

Benefits of using Power Query for concatenation:

  • Better performance for large datasets
  • Concatenation happens during data load, reducing model complexity
  • Easier to handle null values and apply complex transformations if needed
  • Changes are visible immediately in the data preview

Errors when combining fields

  • If you see blank results, check for null values in your columns. In Power Query, you can use the Text.Combine function with null handling:
  Text.Combine({[FirstName], [LastName], [Department], [Position]}, " - ")
  • For performance issues with large datasets, consider creating indexes on frequently used columns in the source database.
  • If special characters cause problems, use the Text.Clean function in Power Query to remove or replace them before concatenation.
  • To handle varying column lengths, use if statements in Power Query to check for blank values before including them in the concatenation.