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 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.
- In the query Preview grid, click the table icon ( ) and click Merge.
- 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.
To merge queries
- In the Merge dialog box:
- Select the primary table from the upper drop-down list, and then select a column by clicking the column header.
- 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.
- 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.
- 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.
After you click OK, the Merge operation creates a new query.
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
- In the Preview grid, click the NewColumn expand icon ( ).
- 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 the column. For more information about how to rename a column, see Rename a column.
Combine data from multiple data sources