How to Merge Columns to Create a New Column in Power Query (Power BI and Excel)

Merge columns (Power Query)

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010

In Power Query, you can merge two or more columns in your query. You can merge columns to replace them with one merged column, or create a new merged column alongside the columns that are merged. You can only merge columns of a Text data type. The following data is used in the examples.

Tip    It’s a good idea to create a new column and keep the original columns so you can better prevent refresh errors that might happen down the road.

Merge columns to replace existing columns

Merge columns to create a new column

You can insert a custom column into the query table and use a custom column to effectively merge two or more columns. In this case, the merged columns are still available along with the new merged column in the query table.

In this example, we are merging OrderID and CustomerID separated by a space character.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.
2. Ensure that the columns that you want to merge are of Text data type. Select Transform > Change Type > Text.
3. Select  Add Column >  Custom Column. The Custom Column dialog box appears.
4. In the Available Columns list, select the first column, and then select Insert. You can also double-click the first column. The column is added to the Custom Column Formula box immediately after the equal sign (=).
   
   Tip    In the Custom Column Formula box, press Ctrl + Space to display columns and other formula elements.
5. In the Custom Column Formula box, enter an ampersand (&) after the first column that you inserted.
   
   The ampersand (&) operator is used to combine values in Power Query in the same way as in Excel.
6. You can specify an additional separator, for example a space character, by entering ” ” after the ampersand (&).
7. In the Custom Column Formula box, enter another ampersand (&) after the space character.
8. In the Available Columns list, select the second column, and then select Insert. You can also double-click the second column. 
   
   
9. Select OK. 

Result

You can rename the custom column so that it is more meaningful to you. For more information, see Rename a column.

Ref: https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9f0a8d