Power BI: Combining columns with Power Query

Short answer

In Power BI you combine columns via the Power Query editor by selecting multiple columns with Ctrl, then right-clicking and choosing 'Merge columns'. You set a separator (space, comma, or none) and give the new column a name. This is useful for merging separate columns such as first name and last name into one full name for use in reports and analyses.

In Power BI it is often necessary to merge data from different columns to create new insights or simplify analyses. This article teaches you how to combine columns in Power BI with Power Query, a powerful and accessible way to transform data.

In Power BI it is often necessary to merge data from different columns to create new insights or simplify analyses. This article teaches you how to combine columns in Power BI with Power Query, a powerful and accessible way to transform data. This is essential for BI consultants and data analysts who want to create efficient and clear datasets.

What you need

A Power BI report with a dataset where multiple columns need to be combined
Access to the Power Query editor within Power BI Desktop
Basic knowledge of the Power Query interface and functions

Step 1: Open the Power Query editor

The Power Query editor is where you transform data before it is loaded into the model. Open it by clicking Home > Transform data in Power BI Desktop. This opens the editor where you can combine columns.

Step 2: Select and combine columns

In the Power Query editor, choose the columns you want to merge by clicking them while holding the Ctrl key. Then right-click on one of the selected columns and choose Merge columns. A dialog box appears where you can choose the separator, for example a space, comma, or no separator. This determines how the values from the columns are merged.

Step 3: Name the new column and adjust if necessary

After merging, a new column appears with the combined values. Give this column a clear name by double-clicking the column header. You can also make further adjustments, such as removing the original columns if you no longer need them. Then click Close and apply to load the changes into your Power BI report.

Tips & common mistakes

Tip: Use a clear separator that suits your data, for example a space for names or a comma for addresses.
Mistake: Don’t forget to give the new column a clear name, otherwise it will be difficult to recognise in your model later.
Tip: Always check after merging whether the data has been correctly combined, especially for empty values in the original columns.

Example: Merging a customer name

Suppose you have a dataset with separate columns for First name and Last name. By combining these with a space as a separator you create a new Full name column. This makes it easier to identify customers in your reports and analyses.

Conclusion

By combining columns in Power BI with Power Query you make your data more organised and more useful for analyses. Try this technique directly in Power BI Desktop or the online environment.

Our tip: Use a clear separator that suits your data, for example a space for names or a comma for addresses.

Frequently asked questions

How do I combine columns in Power BI with Power Query?

Open the Power Query editor via 'Home > Transform data'. Select the columns you want to merge by clicking them while holding the Ctrl key. Then right-click on one of the selected columns and choose 'Merge columns'. Choose a separator and give the new column a clear name. Click 'Close and apply' to load the changes into your report. The original columns are removed by default after merging.

What is the difference between merging columns and adding columns in Power Query?

With 'Merge columns' the selected columns are merged into one new column and the result replaces the original columns. With 'Add custom column' the original columns remain intact and you add an extra column with the combined values via an M formula, for example: [FirstName] & " " & [LastName]. Use merge if you no longer need the original columns; use a custom column if you want to keep them for other purposes in the model.

How do I handle empty values when combining columns in Power BI?

Empty values in one of the columns to be combined result in an empty string in the combined column, which can sometimes lead to double separators or unwanted spaces. Resolve this by using an M formula in a custom column with a check for empty values, such as: if [Column1] = null then [Column2] else if [Column2] = null then [Column1] else [Column1] & " " & [Column2]. Always check the results for unexpected empty or deviating values after merging.