How to combine two or more columns in Power BI tables?

Combine two columns into one in Power BI

To join two our columns in your Power BI data model table, proceed as following:

  1. Open Power BI desktop and bring up your report.
  2. Navigate to the Data View – located at the left hand side of your screen
  3. Highlight the table that contains the two fields you would like to merge.
  4. In the Calculations section of the Ribbon, hit the New Column button.
  5. Then in the formula bar, type the following DAX code – make your required modifications to suite your needs:
new_column =  COMBINEVALUES(delimiter_character ,your_table[your_col_1],your_table[your_col_n])
  1. Check the values of your column and correct in case it’s needed.
  2. Save your BI report.
  3. Use your newly created column in your visual reporting.

Merging two columns into one – Practical Example

In this section we will provide a simple example for concatenating Quarter and Year columns in a table. We later use the merged column in reporting.

Concatenate two columns into one

  • Open your Power BI desktop and navigate to the Data View to familiarize yourself with the dataset.
  • Here’s a snapshot of our data stored in our imaginary Sales_Example column
  • Now, we will create our combined column. Hit the New Column button and add a new column using the DAX syntax shown below:
Quarter-Year = COMBINEVALUES("-", Sales_Example[Quarter], Sales_Example[Year])

Explanation:

The COMBINEVALUES allows to concatenate multiple column values using a predefined delimiter. In our case we concatenated the quarter and year columns into a Quarter-Year column.

Use the new column in your Power BI report

Next we will create a simple visualization to group the sales figures by Quarter.

  • Open the Reports View.
  • Create a new Page if needed.
  • From the Visualization pane, hit the stacked column chart visual.
  • We will drag the Quarter-Year into the X Axis filed, and Sales into the Y Axis.
  • If needed, we will quickly sort the chart X axis values so they make sense.
  • Here’s our simple chart:

Follow up learning

How to calculate days between two dates in Power BI tables?