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.
When you merge columns, the selected columns are turned into one column called Merged. The original two columns are no longer available.
In this example, we merge OrderID and CustomerID.
-
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.
-
Ensure that the columns that you want to merge are the Text data type. If necessary, select the column, and then select Transform > Data Type > Text.
-
Select two or more columns that you need to merge. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column. The order of selection sets the order of the merged values.
-
Select Transform > Merge Columns.
-
In the Merge Columns dialog box, specify a separator to insert between each merged column. You can select from predefined separator values, or specify a custom separator value.
-
Select OK.
Result
You can rename the merged column so that it is more meaningful to you. For more information, see Rename a 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.
-
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.
-
Ensure that the columns that you want to merge are of Text data type. Select Transform > Change Type > Text.
-
Select Add Column > Custom Column. The Custom Column dialog box appears.
-
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.
-
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.
-
You can specify an additional separator, for example a space character, by entering " " after the ampersand (&).
-
In the Custom Column Formula box, enter another ampersand (&) after the space character.
-
In the Available Columns list, select the second column, and then select Insert. You can also double-click the second column.
-
Select OK.
Result
You can rename the custom column so that it is more meaningful to you. For more information, see Rename a column.
