Last week’s blog post on merging tables using Outer joins has proved to be pretty popular. (I guess I’m not the only one who struggled with this concept!) This week we’ll look at the remaining three options, showing how to merge tables using Inner and Anti joins.
Download File:Start File: File: page with all.
Again, we have three join types to explore this week:
In this video, I explain how to combine multiple Excel Tables or Worksheets with Power Query. You will learn how to fully automate this process to save you. Do a standard merge and then filter. Merge the queries with an inner join. Expand the start and end date columns. Select columns satisfying your conditions. Merge two or more tables into one based on key columns with Power Query function (Excel 2016 and later versions) To use the Power Query function for joining multiple tables into one based on the corresponding key columns, please do with the following step by step. 1.If your data ranges are not table format, first, you should convert them to tables, please select the range, and then click. 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.
- Inner Join
- Left Anti Join
- Right Anti Join
If you read last week’s article you may already have an idea of what you’ll be seeing here, but we’ll make sure we go through it in full anyway. (If you HAVEN’T read last week’s article, you might want to do so, as this one just builds on steps that readers will already be comfortable with.)
We’re going to work with the same set of data as we did last week, although we have a different sample file for it (to hold the completed queries.) That file can be downloaded here.
As a reminder, here’s what we’re set up with:
![Query Query](/uploads/1/3/7/5/137564599/671801499.png)
As I explained last week:
… two tables of data, one called Transactions, and one called ChartOfAccounts.
Now, the key piece you want to watch here is that we need the Account-Dept combination to exist in both tables in order to make a perfect join between them. Think VLOOKUP for a second… if you tried to make a VLOOKUP for account 10045 from the Transactions table against the ChartOfAccounts table, what would you get? Of course, you’d get #N/A since 10045 doesn’t exist in the ChartOfAccounts table.
In this case we have items in both tables that don’t exist in the other. (The yellow records in the Transactions table don’t have a match in the ChartOfAccounts table, and the red records in ChartOfAccounts don’t have a match in the Transactions table.) With these differences we can test how each of the first three join types available to us behave when we try to merge the data in both tables together.
We’ve already got the ChartOfAccounts query and Transactions queries set up as connection only queries, so we’re ready to jump right into comparing the join types.
This join type stands out somewhat from the others in that there is no “left” or “right” version. Let’s build the join to explore why that is:
- Open the Workbook Queries pane
- Right click the Transactions query and choose Merge
- Select ChartOfAccounts for the bottom table
- For the top query (Transactions) select Account, hold down CTRL and select Dept
- For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
- Change the Join type to “Inner (only matching rows)”
- Click OK
Like last week, the data lands in Power Query, and we’ll take the following steps to expand the rows:
- Right click the NewColumn column –> Rename –> COA
- Click the Expand icon on the top right of the COA column
- Leave all the defaults and click OK
Merge Tables Excel 2010
The results of these steps are shown here:
We can now see the meaning of the Inner Join. Unlike the Full Outer Join that pulls in all records from both tables whether there is a match or not, the Inner Join only pulls in rows that exist in both the left and right tables. In other words… all those red and yellow rows shown in our original data set? They’re missing from this output.
I can see this being very useful for indentifying matching records, giving a list to show which ones matched without polluting the data set with non-matching items.
Let’s finalize this query and move to the Anti Joins:
- Change the name of the query to Inner
- Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection
Now that we’ve seen Outer and Inner joins, give some thought as to what an Anti join might do…
Done? Let’s go see if you’re right.
- Right click the Transactions query and choose Merge
- Select ChartOfAccounts for the bottom table
- For the top query (Transactions) select Account, hold down CTRL and select Dept
- For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
- Change the Join type to “Left Anti (rows only in first)”
- Click OK
And when the data gets to Power Query:
Excel Power Query Combine Pivot Tables
- Right click the NewColumn column –> Rename –> COA
- Click the Expand icon on the top right of the COA column
- Leave all the defaults and click OK
You should now have this:
So this time, we only see the records from the left table (Transactions) that had no matching record in the ChartOfAccounts (right) table. How cool is that? This allows us to immediately identify records with no matches at all.
![Excel power query merge tables join type Excel power query merge tables join type](/uploads/1/3/7/5/137564599/682077302.jpg)
Let’s finalize this query as well:
- Change the name of the query to Left Anti
- Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection
By this point I’m sure you can predict where this is going. So let’s get to it:
- Right click the Transactions query and choose Merge
- Select ChartOfAccounts for the bottom table
- For the top query (Transactions) select Account, hold down CTRL and select Dept
- For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
- Change the Join type to “Right Anti (rows only in second)”
- Click OK
And when the data gets to Power Query:
- Right click the NewColumn column –> Rename –> COA
- Click the Expand icon on the top right of the COA column
- Leave all the defaults and click OK
And, in anti-climactic fashion (ha!) we end up with the following:
Excel Power Query Merge Updated Tables
Yes indeed, as you probably predicted, only the records shown highlighted in yellow in the ChartOfAccounts (right) table show up in this set. No record that has a match in the Transactions (left) table, nor any records in the Transactions table without a match in the ChartOfAccounts table show up.
We can now finish our final comparison:
- Change the name of the query to Right Anti
- Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection
I think this is pretty cool stuff. If you’d asked me how many ways there are to join data, I think I would have been hard pressed to answer six until I wrote this up. But seeing it laid out in full, with each option detailed… I can see where these would each be useful in their own right. I hope you enjoyed taking the journey with me here!
Oh… and by the way… if you’d like to download one workbook with all six join types included… you can do that here.
-->One of the join kinds available in the Merge dialog box in Power Query is a full outer join, which brings in all the rows from both the left and right tables. More information: Merge operations overview
Figure shows a table on the left with Date, CountryID, and Units columns. The emphasized CountryID column contains values of 1 in rows 1 and 2, 3 in row 3, and 2 in row 4. A table on the right contains ID and Country columns. The emphasized ID column contains values of 1 in row 1 (denoting USA), 2 in row 2 (denoting Canada), 3 in row 3 (denoting Panama), and 4 (denoting Spain) in row 4. A table below the first two tables contains Date, CountryID, Units, and Country columns. All rows have been rearranged in numerical order according to the CountryID value. The country associated with the CountryID number is shown in the Country column. Because the country ID for Spain wasn't contained in the left table, a new row is added, and the date, country ID, and units values for this row are set to null.
This article uses sample data to show how to do a merge operation with the full outer join. The sample source tables for this example are:
- Sales: This table includes the fields Date, CountryID, and Units. CountryID is a whole number value that represents the unique identifier from the Countries table.
- Countries: This is a reference table with the fields id and Country. The id field represents the unique identifier for each record.
In this example, you'll merge both tables, with the Sales table as the left table and the Countries table as the right one. The join will be made between the following columns.
Field from the Sales table | Field from the Countries table |
---|---|
CountryID | id |
The goal is to create a table like the following, where the name of the country appears as a new Country column in the Sales table. Because of how the full outer join works, all rows from both the left and right tables will be brought in, regardless of whether they only appear in one of the tables.
Full outer join final table with Date, a CountryID, and Units derived from the Sales table, and a Country column derived from the Countries table. A fifth row was added to contain data from Spain, but that row contains null in the Date, CountryID, and Units columns since those values did not exist for Spain in the Sales table.
To perform a full outer join
- Select the Sales query, and then select Merge queries.
- In the Merge dialog box, under Right table for merge, select Countries.
- In the Sales table, select the CountryID column.
- In the Countries table, select the id column.
- In the Join kind section, select Full outer.
- Select OK
Tip
Take a closer look at the message at the bottom of the dialog box that reads 'The selection matches 4 of 4 rows from the first table, and 3 of 4 rows from the second table.' This message is crucial for understanding the result that you get from this operation.
In the Countries table, you have the Country Spain with id of 4, but there are no records for CountryID 4 in the Sales table. That's why only three of four rows from the right table found a match. All rows from the right table that didn't have matching rows from the left table will be grouped and shown in a new row in the output table with no values for the fields from the left table.
From the newly created Countries column after the merge operation, expand the Country field. Don't select the Use original column name as prefix check box.
After performing this operation, you'll create a table that looks like the following image.
Draw the sigil with red paint on a black or white background and place it on the altar. Light the candles around it and burn incense. Fill your chalice with red wine and a few drops of your blood, as the wine is Naamah's blood that is mixed with a few drops of yours as a communion. The Charmer; Mother of Divination; My Darling. Naamah, ancient and mysterious spirit, is a sometime ally, sometime rival, sometime traveling companion of Lilith.Like Lilith, she takes many forms: Naamah is a Demon, an angel (albeit of prostitution), and a primordial goddess. Login; Naamah sigil. Who is naamah. Demon Sigil Naamah Hoodie moribundus. 5 out of 5 stars (26) Sale Price $34.00 $ 34.00 $ 40.00 Original Price $40.00' (15% off) Favorite Add to BALM - Swarovski Teardrop Necklace SonderIsleCreations. 5 out of 5 stars (9) $ 29.00 FREE shipping Favorite Add to Queen Naamah on Throne set Dungeons and Dragons Wargames Warhammer Age of Sigmar AoS.
Full outer join final table containing Date, a CountryID, and Units derived from the Sales table, and a Country column derived from the Countries table. A fifth row was added to contain data from Spain, but that row contains null in the Date, CountryID, and Units columns since those values didn't exist for Spain in the Sales table.