How to understand the difference between left outer and inner joins in Excel Power Query

In a previous post, we looked at some of the limitations of XLOOKUP() and other Excel “duct tape” lookup functions. Relational joins in Power Query were introduced as a more robust alternative. This post serves as an introduction to actually executing some of those joins in Power Query.

For a first step, both sales and countries get loaded into Power Query.

Performing the left outer join

As a reminder, a left outer join returns all records from the first table, with null results for any unmatched records in the second table.

To get started on the merge, have the sales query selected in Power Query. Then head to Merge Queries on the ribbon and Merge Queries as New:

From here do the following to complete the left join:

1. Select countries from the second dropdown menu.
2. Click on country_id in both tables to identify it as the column to merge on.
3. Confirm Left Outer as the desired Join Kind in the third dropdown menu.

Click OK and you will see the merge results in a query named Merge1. Go ahead and double-click that name to rename the query left before we continue.

The data in our query looks a little unusual right now, specifically with the countries column. Go ahead and click the button to expand the selection of columns that were merged into the query.

Because country_id is already found in the countries table, I don’t want it repeated in the table. I will also check off “Use original column name as prefix” so the country_name label remains as is.

Just as expected, we see all of the original rows from sales, with an additional country_name column. No match was found for country 99, so a null results.

Performing the inner join

Next, let’s create an inner join. But before you do that, see if you can explain to yourself how an inner join works and what results you can expect. You can even refer back to this blog post as a resource.

Following the same steps as above, perform the inner join in Power Query. You can expand the matching columns in the same manner as before, then rename the query inner.

Your resulting query should look like this:

As anticipated, because country 99 is not found in each table, it’s dropped from the results:

But wait, there’s more

While the left and inner are the most common join types, others exist and can also be performed in Power Query.

Have you started merging your data in Power Query? How do you find them compared to lookup functions like VLOOKUP() and XLOOKUP()? Let me know in the comments.