How to Merge Queries in Power Query

Do you want to do a lookup in Power Query, or just join multiple tables together? In this post, I’ll show you how you can do that. The first thing you need to do is set up each individual query so that it is accessible in Power Query.

In my data set, I have various indicators for countries across the world. In one table, I have the data and the country code:

On another table, I have a list of those country codes and more detailed information about which parts of the world they relate to:

Naturally, I want to combine this information. It’s the equivalent of doing a lookup, except within Power Query. I can do a lookup before populating the data into Power Query, but I can also just merge the queries.

Once you have the queries loaded in Power Query, you can go ahead and start merging them. There is a Merge Queries button on the Home Tab, in the Combine section:

For best practices, you should switch to your main query, the one that holds the data you’ll primarily be using, and then click on the button. By doing this, you can avoid having to adjust the join type. Once you press the Merge Queries button, you’ll see the following options:

The Data query is the initial one that shows up as that is the one I was on when clicking the merge button. I’ll have to select a table I want to merge with (in this case, it will be the one with the country information). After selecting the table to merge with, I’ll also need to highlight the columns that connects the two queries. In this case, it is the Country Code, which I’ve highlighted in both tables:

It doesn’t need to be a one-to-one relationship but if it isn’t, then a single row will end up expanding into more for each match that’s found. The last thing you need to specify before deploying the merge is determining the join kind. There are several options for this:

If you don’t want to lose any data from your main table, then you’ll want to look at one of the first three options. In this situation, where you’re adding data from another table, you’ll either use the Left Outer or Right Outer join. This is where first selecting your main table before clicking on the merge button will make this easier for you. That’s because since it would be the first table, a Left Outer join (the default option) would suffice. In a Left Outer join, you’re keeping all the records from the initial table and only adding matching ones from the second. If your first table is the main one you’ll want to be using, then the Left Outer join will work best. If you didn’t do that, then the Right Outer will be what you want.

When in doubt, look at the description in parentheses to guide your decision to see what each join will do. Once you’ve selected the join type, click on OK. Now, you should see a new column that contains tables for each row:

To expand these tables, you can click on the button in the Countries header, which shows two arrows going in opposite directions:

When you click on that, you’ll be able to select all the fields that you want to extract from the other table:

For this purpose, I’ll only leave the Short Name checked off since I don’t want to make my query unnecessarily large. I’ll also uncheck the tick box at the bottom that by default will leave the original column name as a prefix. After clicking OK, I now have the short name populated in my main query. All that’s left is to move the short name back to the beginning, next to the country code. Now my merge looks complete:

If you liked this post on How to Merge Queries in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.