When you merge, you typically join two queries that are either within Excel or from an external data source. In addition, the Merge feature has an intuitive user interface to help you easily join two related tables. For an example of merging total sales from an order details query into a products table, see the Learn to combine multiple data sources tutorial.
Caution: 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 Set privacy levels.
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 relationship to a secondary 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.
There are two types of merge operations:
-
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.
-
Intermediate Merge You create a new query for each merge operation.
To see a visual representation of the relationships in the Query Dependencies dialog box, select View > Query Dependencies. At the bottom of the dialog box, select the Layout command to control the diagram orientation.
You need at least two queries that can be merged and that have at least one or more columns to match in a join operation. They can come from different types of external data sources. The following example uses Products and Total Sales.
-
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.
-
Select Home > Merge Queries. The default action is to do an inline merge. To do an intermediate merge, select the arrow next to the command, and then select Merge Queries as New.
The Merge dialog box appears. -
Select the primary table from the first drop-down list, and then select a join column by selecting the column header.
-
Select the related table from the next drop-down list, and then select a matching column by selecting the column header.
Ensure that you select the same number of columns to match in the preview of the primary and related or secondary tables. Column comparison is based on the order of selection in each table. Matching columns must be the same data type, such as Text or Number. You can also select multiple columns to merge.
-
After you select columns from a primary table and related table, Power Query displays the number of matches from a top set of rows. This action validates whether the Merge operation was correct or whether you need to make changes to get the results you want. You can either select different tables or columns.
-
The default join operation is an inner join, but from the Join Kind drop down list, you can select the following types of join operations:
Inner join Brings in only matching rows from both the primary and related tables.
Left outer join Keeps all the rows from the primary table and brings in any matching rows from the related table.
Right outer join Keeps all the rows from the related table and brings in any matching rows from the primary table.
Full outer Brings in all the rows from both the primary and related tables.
Left anti join Brings in only rows from the primary table that don't have any matching rows from the related table.
Right anti join Brings in only rows from the related table that don't have any matching rows from the primary table.
Cross join Returns the Cartesian product of rows from both tables by combining each row from the primary table with each row from the related table. -
If you want to do a fuzzy match, select Use fuzzy matching to perform the merge and select from the Fuzzy Matching options. For more information, see Create a fuzzy match.
-
To include only those rows from the primary table that match the related table, select Only include matching rows. Otherwise, all rows from the primary table are included in the resulting query.
-
Select OK.
Result
After a Merge operation, you can expand the Table structured column to add columns from the related table into the primary table. Once a column is expanded into the primary table, you can apply filters and other transform operations. For more information, see Work with a List, Record, or Table structured column.
-
In the Data Preview, select the Expand
-
In the Expand drop-down box, select or clear the columns to display the results you want. To aggregate the column values, select Aggregate.
-
You may want to rename the new columns. For more information, see Rename a column.
See Also
Learn to combine multiple data sources
