Power Query Merge Queries

-->

At one go, you can merge only two tables in Power Query. So we will first have to merge Table 1 and Table 2 and then merge Table 3 into it in the next step. Merging Table 1 and Table 2. To merge tables, you first need to convert these tables into connections in Power Query. Once you have the connections, you can easily merge these. Here are the steps to save an Excel table as a connection in Power Query. Doing a merge operation in Power Query is pretty simple, just click the Merge Queries button, select your tables and field (s) and away you go. But what if you want to merge Table A to a subset of Table B? You can do it, but you need to tweak your M code manually in the Advanced Editor. Merge the two queries first using Product Name as key - Then expand the ImageIDs column in the merged query - Split the ImageIDs column into rows. Group the table in Query 1 by Product Name - Merge the two queries using Product Name as key (which now has only unique values on each query) - expand the name table in the merged query. The result of a combine operation on one or more queries will be only one query. You can find Append or Merge in the Combine Queries section of the Query Editor in Power BI or in Excel. Append means results of two (or more) queries (which are tables themselves) will be combined into one query in this way: Rows will be appended after each other.

A merge queries operation joins two existing tables together based on matching values from one or multiple columns. You can choose to use different types of joins, depending on the output you want.

Merging queries

You can find the Merge queries command on the Home tab, in the Combine group. From the drop-down menu, you'll see two options:

  • Merge queries: Displays the Merge dialog box, with the selected query as the left table of the merge operation.
  • Merge queries as new: Displays the Merge dialog box without any preselected tables for the merge operation.
Power Query Merge Queries

Identify tables for merging

The merge operation requires two tables:

Merge queries (Power Query) Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 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.

  • Left table for merge: The first selection, from top to bottom of your screen.
  • Right table for merge: The second selection, from top to bottom of your screen.

Note

The position—left or right—of the tables becomes very important when you select the correct join kind to use.

Select column pairs

After you've selected both the left and right tables, you can select the columns that drive the join between the tables. In the example below, there are two tables:

  • Sales: The CountryID field is a key or an identifier from the Countries table.
  • Countries: This table contains the CountryID and the name of the country.

Merge dialog box with the Left table for merge set to Sales and the CountryID column selected, and the Right table for merge set to Countries and the CountryID column selected.

The goal is to join these tables by using the CountryID column from both tables, so you select the CountryID column from each table. After you make the selections, a message appears with an estimated number of matches at the bottom of the dialog box.

Note

Although this example shows the same column header for both tables, this isn't a requirement for the merge operation. Column headers don't need to match between tables. However, it's important to note that the columns must be of the same data type, otherwise the merge operation might not yield correct results.

You can also select multiple columns to perform the join by selecting Ctrl as you select the columns. When you do so, the order in which the columns were selected is displayed in small numbers next to the column headings, starting with 1.

For this example, you have the Sales and Countries tables. Each of the tables has CountryID and StateID columns, which you need to pair for the join between both columns.

Queries

First select the CountryID column in the Sales table, select Ctrl, and then select the StateID column. (This will show the small numbers in the column headings.) Next, perform the same selections in the Countries table. The following image shows the result of selecting those columns.

![Merge dialog box with the Left table for merge set to Sales, with the CountryID and StateID columns selected, and the Right table for merge set to Countries, with the CountryID and StateID columns selected. The Join kind is set to Left outer.

Expand or aggregate the new merged table column

After selecting OK in the Merge dialog box, the base table of your query will have all the columns from your left table. Also, a new column will be added with the same name as your right table. This column holds the values corresponding to the right table on a row-by-row basis.

From here, you can choose to expand or aggregate the fields from this new table column, which will be the fields from your right table.

Table showing the merged Countries column on the right, with all rows containing a Table. The expand icon on the right of the Countries column header has been selected, and the expand menu is open. The expand menu has the Select all, CountryID, StateID, Country, and State selections selected. The Use original column name as prefix is also selected.

Note

Currently, the Power Query Online experience only provides the expand operation in its interface. The option to aggregate will be added later this year.

Join kinds

A join kind specifies how a merge operation will be performed. The following table describes the available join kinds in Power Query.

Join kindIconDescription
Left outerAll rows from the left table, matching rows from the right table
Right outerAll rows from the right table, matching rows from the left table
Full outerAll rows from both tables
InnerOnly matching rows from both tables
Left antiOnly rows from the left table
Right antiOnly rows from the right table

Fuzzy matching

Power Query Merge Data

You use fuzzy merge to apply fuzzy matching algorithms when comparing columns, to try to find matches across the tables you're merging. You can enable this feature by selecting the Use fuzzy matching to perform the merge check box in the Merge dialog box. Expand Fuzzy matching options to view all available configurations.

Note

Fuzzy matching is only supported for merge operations over text columns.

This past weekend I attended SQL Saturday in Portland, OR. While I was there, I attended Reza Rad’s session on Advanced Data Transformations with Power Query. During that session, Reza showed a cool trick to merge data based on two columns through the user interface… without concatenating the columns first.

Assume for a second that we have data that looks like this:

There’s two tables, and we want to join the account name to the transaction. The problem is that the unique key to join these two tables (which isn’t super obvious here) is a combination of the Acct and Dept fields. (Elsewhere in the data the same account exists in multiple departments.

To get started, I created two connection only queries, one to each table.

  • Select a cell in the left table (Transactions) –> create a new query –> From Table –> Close & Load To… Connection only
  • Select a cell in the right table (COA) –> create a new query –> From Table –> Close & Load To… Connection only

My Original Approach

Now, with both of those created, I want to merge the data so I get the account name on each row of the Transactions table. So how…?

Originally I would have edited each query, selected the Acct and Dept columns, and merged the two columns together, probably separating them with a custom delimiter. (This can be done via the Merge command on the Transform or the Add Column tab.)

Essentially, by concatenating the columns, I end up with a single column that I can use to dictate the matches.

Reza’s presentation showed that this isn’t actually necessary, and I don’t need to merge those columns at all…

So here’s how we can get those records from the COA Table into the Transactions table:

  • Right click the Transactions query in the Workbook Queries pane
  • Choose Merge
  • Select the COA query

The data now looks like this, asking for us to select the column(s) we wish to use for the merge:

So here’s the secret:

  • Under Transactions, click the Acct column
  • Hold down the CTRL key
  • Click the Dept column

And Power Query indicates the order of the columns you selected. It will essentially use this as a temporary concatenated value!

So now do the same to the COA table:

Power Query Merge Queries As New

And then complete the merge. As you can see, you get a new column of data in your query:

of course, we can expand NewColumn to get just the Name field, and everything is working perfectly!

This is pretty cool, although not super discoverable. The really nice piece here is that it can save you the work of creating extra columns if you only need them to merge your data.

Excel Power Query Merge Queries

I should also mention that Reza showed this trick in Power BI Desktop, not Excel. But because it’s Power Query dealing with the data in both, it works in both. How cool is that?