Merge queries (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.

A merge query creates a new query from two existing queries. One query result contains all columns from a primary table, with one column serving as a single column containing a navigation link to a related table. The related table contains all rows that match each row from a primary table based on a common column value. An Expand operation adds columns from a related table into a primary table.

For an example of merging total sales from an order details query into a products table, see the Combine data from multiple data sources tutorial.

With a merge query, you can achieve similar goals to the Excel VLOOKUP function. VLOOKUP lets you do a join between two data sets that are in Excel. With a merge, you can join two data queries that are in Excel or an external data source. In addition, the Merge feature has an intuitive user interface to easily relate the two join tables.

Security Note: Privacy Levels prevent a user from inadvertently combining data from multiple data sources, which may be private or organizational. Depending on the query, a user could inadvertently send data from the private data source to another data source that might be malicious. Power Query analyzes each data source and classifies it into the defined level of privacy: Public, Organizational, and Private. For more information about Privacy Levels, see Privacy Levels.

You can perform two types of merge operation, Inline Merge or Intermediate Merge. With Intermediate Merge, you create a new query for each merge operation. With Inline Merge, you merge data into your existing query until you reach a final result. The result is a new step at the end of the current query.

To do an inline merge : In the query Preview grid, click the table icon ( Table icon ) and click Merge.

Merge Queries

To do an intermediate : In the POWER QUERY or Query Editor ribbon, in the Combine group, click Merge. When using the Query Editor ribbon, the active query is selected as the primary table for the Merge operation.

Merge Queries in Power Query

  1. In the Merge popup window

    • Choose the primary table from the upper drop-down list, and then choose a column by clicking the column header.

    • Choose the related table from the lower drop-down list, and then choose a matching column by clicking the column header.

      Note: You can select multiple columns to merge. Ensure that you select the select the same number of columns to match in the primary table and related table preview.

      After you select columns from a primary table and related table, Power Query displays the number of matches out of the top rows. This action validates whether the Merge operation was correct or whether you need to make changes to Merge settings or to the queries that you want to merge.

  2. Check the Only include matching rows box to include only those rows from the primary table that match with the related table in the resulting merge query. If you do not check this box, all the rows from your primary table will be included in the resulting merge query.

  3. Click OK.

    Note: The common columns in the primary table and the related table are compared based on the order selected for each table. In addition, columns must be the same type, such as Text or Number, so that they match.


    In the figure below, you can see a merge between a Products primary table and a Total Sales related table.

    Merge dialog box

After you click OK, the Merge operation creates a new query.

Merge Final

After a Merge operation, you can expand a Table link column to add columns from the related table into a primary table. Once a column is expanded into the primary table, you can apply filters and other transform operations.

To expand a column, follow these steps:

  1. In the Preview grid, click the NewColumn expand icon ( Expand ).

  2. In the Expand drop-down:

    • Click (Select All Columns) to clear all columns.

    • Click the columns from the related table to add to the primary table.

    • Click OK.

Note: After you expand a column, you can rename it. To learn more about how to rename a column, see Rename a column.

Power Query Merge

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

Combine data from multiple data sources

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.

×