Merge queries (Power Query)

Important   This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.

The Merge operation 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 Merge, you can achieve similar goals to the Excel VLOOKUP function. VLOOKUP lets you do a join between two data sets that are in Excel. Merge lets you join two data queries that are in Excel or from an external data source. In addition, Merge 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.

Perform a Merge operation

You can perform two types of merge operation: Intermediate Merge or Inline 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.

Inline Merge

  1. In the query Preview grid, click the table icon (Table icon) and click Merge.

    Merge Queries

Intermediate Merge

  1. 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

To merge queries

  1. In the Merge dialog box:

    1. Select the primary table from the upper drop-down list, and then select a column by clicking the column header.

    2. Select the related table from the lower drop-down list, and then select 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. Select the Only include matching rows check box to include only those rows from the primary table that matched with the related table in the resultant merge query. If you do not select this check box, all the rows from your primary table will be included in the resultant 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.

    The following Merge example illustrates a Merge operation 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

Perform an Expand operation

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.

Expand a column

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

  2. In the Expand drop-down:

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

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

    3. Click OK.

Note    After you expand a column, you can rename the column. For more information about how to rename a column, see Rename a column.

Power Query Merge

Related Topics

Combine data from multiple data sources

Applies To: Excel 2010, Excel 2013

Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Change language