Office
Sign in

Merge columns (Power Query)

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

Note: For a quick video on how to display Query Editor, see the end of this article.

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

  1. In Query Editor, ensure that the columns that you want to merge are of Text data type. Right-click a column header, and select Change Type > Text from the context menu.

  2. Select two or more columns that you need to merge. Press the CTRL key, and then click on the column headers to select each of the columns that you'll include in the merge.

    NOTE: The order in which you select the columns sets the order of the values in the merged column.

  3. Right-click the columns and click Merge Columns.

    Merge columns
     

    Note: The Merge Columns command is available only if all the columns selected for the merge operation are of Text data type.

  4. In the Merge Columns popup window, specify the separator that is in use between each of the column values. You can select from predefined separator values, or specify a custom separator value.

    Select a seperator

  5. Click OK to create a merge column that replaces the columns selected for the merge operation. Rename the merged column so that it is more meaningful to you..

    Merged column

You can insert a custom column into the query table and use a custom column formula to merge values in two or more columns. In this case, the existing columns that are merged are available alongside the new merged column in the query table.

To merge column values by inserting a custom column:

  1. In Query Editor, ensure that the columns that you want to merge are of Text data type. Right-click a column header, and select Change Type > Text.

  2. Click the table icon ( Table icon ), and then click Insert Custom Column. Optionally, right-click a column header and then click Insert Custom Column.

    Insert custom column

  3. In the Insert Custom Column popup window:

    1. Double-click the first column that you need to merge from the Available Columns list or click the column from the Available Columns list, and click Insert. The column gets added to the Custom Column Formula box.

    2. In the Custom Column Formula box, type & after the first column that you inserted. The & operator is used to combine values.

    3. Specify the separator between the values. In this example, specify space as the separator between the values by specifying " ".

    4. Type "&" after the " ".

    5.  Type & after " ".

    6. Specify the second column that you need to merge after &by double-clicking the column name in the Available Columns list. Or, click the column from the Available Columns list, and then clicking Insert. In this example, we merge the same columns as earlier (OrderID and CustomerID), separated by a space.

      Specify custom column formula to merge column values
       

  4. Click OK to create a custom column at the end of the query table with merged values from the original columns. In this case, the original columns are also available in the query table. Rename the custom column name as required.

    Custom merged column

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The video below shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The video shows one way to display the Query Editor.

How to see Query Editor in Excel

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The video below shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The video shows one way to display the Query Editor.

How to see Query Editor in Excel

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Insert a custom column into a table

Shape data

Microsoft Power Query for Excel Help

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×