Combine two columns into one in Power BI
To join two our columns in your Power BI data model table, proceed as following:
- Open Power BI desktop and bring up your report.
- Navigate to the Data View – located at the left hand side of your screen
- Highlight the table that contains the two fields you would like to merge.
- In the Calculations section of the Ribbon, hit the New Column button.
- 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])
- Check the values of your column and correct in case it’s needed.
- Save your BI report.
- 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])
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: