Merge queries (Power Query) - Microsoft Support (2024)

A merge querycreates 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 thebottom 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.

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

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

3. Select the primary table from the first drop-down list, and then select a join column by selecting the column header.

4. Select the related table from the next drop-down list, and then select a matching column by selecting the column header.

   See Also

   Connect to a Power Query data source - Dynamics 365 Customer InsightsLearn to combine multiple data sources (Power Query)Power Query Folder connector - Power QueryCreate a Data Model in Excel

   Ensure that you select the same number of columns to match in the preview of the primary and related or secondary tables. Column comparisonis based on the order of selectionineach table.Matching columns must be the same data type, such as Text or Number.You can also select multiple columns to merge.

   

5. 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 resultsyou want. You can either select different tables or columns.

6. The default join operation is an inner join, but from the Join Kind drop down list, you can selectthe 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.

7. If you want to do a fuzzy match, selectUse fuzzy matching to perform the merge and select from the Fuzzy Matching options. For more information, see Create a fuzzy match.

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

9. SelectOK.

Result

After a Merge operation, you can expand theTablestructured 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.

1. In the Data Preview, select the Expand icon next to theNewColumn column header.

2. In the Expand drop-down box, select or clear the columns to display the results you want. To aggregate the column values, select Aggregate.

   

3. You may want to rename the new columns. For more information, see Rename a column.