Compare Two Columns with Microsoft Excel Power Query

If you’re looking to compare two columns in Excel with Power Query, you’ve come to the right place. In this tutorial, you’ll learn how to use Power Query to compare two lists to find which items appear on both and which items appear on only one list. Plus, you’ll learn how to match on multiple columns.

This post is brought to you by The CFO Project. They teach accountants and bookkeepers how to offer CFO/business advisory services so they can grow their practices and escape “The Accountant’s Trap”. The accountant’s trap is where accountants are not getting paid for their value and are forced to work long hours with high-demanding clients with little pay. Check out their free Masterclass where they’ll show you how to start a CFO service this year and finally escape the accountant’s trap. Click here.

Video

Step-by-step guide

We’ll walk through a couple exercises.

Exercise 1

In this exercise, we have two single-column lists, like this:

We’d like to know which items appear in both lists. Then, which items appear in only one list.

Either way, the first step is to get these two lists into Power Query. To do this, select any cell in the first table and click the Data > From Table/Range command. In the resulting Power Query Editor, select Close & Load To and then Connection Only Query. Do this again for the second table.

Now that both tables are stored as Connection-only queries, we can do the comparison. Select Get Data > Combine Queries > Merge. In the resulting dialog, pick the first table (List1) and then the second table (List2). To specify the lookup column, select the column from each table that needs to match. In this case, it is the List1 and List2 columns. Finally, select the desired Join Kind. Since we want to identify items that appear in both lists, we select Inner:

In the resulting Power Query Editor, delete any unneeded column, such as List2, and then Close & Load To a Table. Bam:

Power Query returns a table that includes a list of items found in both lists.

Once you have the list of items that appear in both lists, you can easily modify the query to do exception reporting and find the items that appear in one list only.

To do this, open the Power Query editor and click the Source > Gear Icon in the Applied Steps list box:

In the resulting Merge dialog, change the Join Kind from Inner to Left Anti.

Click OK and then Close & Load and the Power Query results table will show items found in the first table only:

You can just as easily update the query to show items found in the second table only by changing the Join Kind to Right Anti.

Can we use a similar approach when there are multiple columns in our tables? Yes! Let’s tackle that next.

Exercise 2

In this exercise, we have two tables. Table 1:

And Table 2:

And we want to find out which items in Table 1 also appear in Table 2.

Just as before, the first step is to get these tables into Power Query. We select the first table and use the Get Data > From Table/Range command. Close & Load To … a Connection Only Query. Do the same thing for the second table. With both tables in Power Query, go to Get Data > Combine Queries > Merge.

In the resulting Merge dialog, pick Table1 and Table2 and then select the column (or columns) that must match. You’ll need to pick the columns in the same order (Product ID, Option, Cost). Then, select a Join Kind of Inner to generate a list of the items appearing in both tables:

In the resulting Power Query editor, remove any unneeded columns and make other transformations as desired. Close & Load To a Table, and bam! Now you have a list of items that appear in both tables:

You can change the join kind to Left Anti or Right Anti if you are looking to identify which items appear only in one of the tables.

Note: if you wanted to compare the cost columns from both tables, you could set the lookup columns to ProdID and Option only (and not match on Cost). Then, use the Power Query editor to display both cost columns. You can also select both cost columns, and then add a new column that computes the difference. Demo of this is provided in the video above.

Conclusion

I hope this summary of how to compare two columns with Excel’s Power Query was helpful. If you have any suggestions, questions, or alternatives, please share by posting a comment below … thanks!

Sample file