How to combine different columns in Power BI?

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],
    ", "
)

4. Click the checkmark or press Enter to create the measure.
5. 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]

4. 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]

6. Click OK to create the new column.
7. If needed, you can modify the data type of the new column to Text.
8. Close & Apply to save your changes and return to Power BI Desktop.
9. 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

Choose the combination layer deliberately

A dependable column combination fix starts with a reproducible test, not a long list of unrelated changes. Preserve the current configuration, note the time and exact symptom, and change one variable at a time. That approach makes the result reversible and gives an administrator useful evidence if the issue needs escalation.

Run a focused validation

Combine columns in Power Query when the result is part of data preparation and should be reused by every report. Use a DAX calculated column when the combined label depends on the loaded model, and prefer a measure when the text should respond to filter context.

Set data types before concatenating. Convert numbers and dates with an explicit format and culture, replace or handle nulls, and choose a separator that cannot be confused with real data. Preserve the original fields until validation is complete.

Confirm the result

Check representative rows containing nulls, delimiters, international dates, and long text. If the combined value will act as a key, test uniqueness rather than assuming concatenation creates it. For display labels, sort by a stable numeric or date column so textual ordering does not distort the report.

Repeat the original action after every material change and record what improved. If the test fails, restore the previous state before trying the next step. This keeps the troubleshooting path clear and avoids creating a second problem while resolving the first.

For a managed work device, also check whether the setting is enforced by organizational policy. A control that is unavailable, returns after sign-in, or behaves differently for another account may be intentional rather than broken. Do not bypass that policy locally. Instead, capture the device name, Windows or application version, account type, network used, and screenshots of the relevant setting. Share only non-sensitive evidence with support. This information lets the administrator reproduce the condition, compare policy assignments, and decide whether the solution belongs on the device, in the user profile, or in the Microsoft 365 administration layer.

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.