We explain how to concatenate in Power BI using a CONCATENATE function and look at alternative methods that don’t limit you to combining only two columns of text at a time. The Power Query editor can be used to more quickly and easily combine multiple columns at a time and handle special characters.
Using the CONCATENATE Function in Power BI
If you only need to combine two fields or columns in Power BI, the question way to combine them is with the CONCATENATE Formula.
Power BI uses a formula language called DAX and it’s what people use to create measures. Measures are another name for saved formulas in Power BI. To create a CONCATENATE Measure follow these steps:
- Click in the Table that you want to create a new concatenated column.
- Select the Column Tools section at the top of the Power BI toolbar.
- You will be prompted in the formula bar to type in a formula. Use the following syntax:
New Column Name = CONCATENATE(‘Table'[Column1]’,’Table'[Column2])
The new column will be named whatever you type before the = sign. The following part tells Power BI to use a CONCATENATE formula and enter it as First Column ,Second Column with a comma between the two column names.
Note: The CONCATENATE formula will only allow you to combine two columns together.
If you need to add a special character like a – or a space between the two columns, you can type: &”-” or any special character you need between the quotes like the example below.
How to Concatenate Multiple Columns in Power BI
If you need to concatenate multiple columns in Power BI, you will not be able to do so with the concatenate function. Follow these instructions instead:
Open the Power Query editor. This is the screen that lets you modify and transform data that you build reports and visualizations off of in Power BI. You can get to the Power Query editor by clicking on Transform Data in the Power BI toolbar, or right click on a dataset in the data panel to the right and click on Edit Query.
- From the Power Query Editor, click on Custom Column to create a new column.
- You will be shown the Power Query Formula Editor where you can type formulas in that will populate the new Custom Column
- The syntax for the Custom Column editor is:
[Column Name] & [Column2 Name]
You can also put special characters such as spaces and underscores in quotes between the two column names.
Note: When typing formulas into Power BI you can type in a left square bracket, and a menu will populate with all of the available column names. This will save type and prevent you from mistyping a column name to ensure that the formula will work as expected.
Concatenating multiple columns using the Power Query Editor is the most similar way to concatenating columns in Microsoft Excel. The Power Query engine was initially developed as a tool for Microsoft Excel. You will still see it in use if you use the Get and Transform features in Excel.
How to Concatenate Multiple Columns From Examples
If you’re not big on using formulas, Microsoft has provided the option for users to create an Example Column in Power BI and the software will write the formula for you.
To use this advanced Power BI feature click on Column From Examples in the top right of the Power Query Editor.
You will be presented with a new Merged Column at the far right of the data set.
Type in an example of what you want the new column to look like based on existing data. Power BI will detect the column names and show a suggestion of what the column will look like.
By adding a column from an example you can quickly and easily concatenate Power BI columns together without using any formulas.
While Power BI has a CONCATENATE function that lets you combine columns together it’s limited to only allowing you to combine 2 columns at a time.
To concatenate more than 2 columns, use the Power Query editor where you can create a custom column and combine columns with an & symbol.
The Column from Example feature of Power Query will let you concatenate as many columns as you need to by providing an example of what you want the end result to look like based on the starting data.