Compare Two Tables with Power Query in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

When working with tables, it is a frequent need for us to compare two tables to each other if their values are the same or different or can be linked and compared by some other means. This comparison can be done through power query easily. In this article, I will show you 2 practical examples to compare two tables through Excel Power Query.

Table of Contents hide

Download Practice Workbook

2 Examples to Compare Two Tables Through Power Query in Excel

1. Comparing Two Tables and Merge All Values

2. Compare Two Tables with Power Query and Find the Common Values

Conclusion

Download Practice Workbook

You can download our practice workbook from here for free!

Comparing Two Tables with Power Query.xlsx

2 Examples to Compare Two Tables Through Power Query in Excel

Say, you have a dataset of the Students’ Records with their student ids and names.

And, you have a dataset for the Passed Students’ Records in Term -1 and Term -2 individually.

Now, you need to compare the Passed Students Records dataset for two terms individually and you need to find the students who passed in both terms and the students who passed at least one term. Follow the methods below to learn and accomplish this through the Excel Power Query tool.

1. Comparing Two Tables and Merge All Values

First, we need to find the students who passed at least one term. That means, we have to compare and merge the two tables’ values. You can compare two tables and merge all values through Power Query by following the steps below.

📌 Steps:

First and foremost, you need to convert all your dataset ranges into tables.
To do this, click on any cell of the Students Record range >> go to Insert tab >> Tables group >> Table tool.

As a result, the Create Table window will appear.
Following, choose the range as B4:C14 >> tick on the option My table has headers >> click on the OK button.

As a result, you will see your Students Record dataset converted into a table.

Now, to name this table, click on any value inside the table >> go to the Table Design tab >> write Students in the Table Name: text box.

Similarly, create two more tables named Term_1 and Term_2 from the individual term’s passed students’ records.

At this time, to go to the Power Query window, right-click on any cell inside the Students Record table and choose the option Get Data from Table/Range option from the context menu.

As a result, the Students table is shown in the Power Query too.

Afterward, at the Home tab of the Power Query, click on the arrow inside the Close & Load button >> choose the option Close & Load To…

Consequently, the Import Data window will appear.
Subsequently, put the radio button on the option Only Create Connection and click on the OK button.

Repeat the previous procedures again individually for the Term_1 and Term_2 tables to import data from the given tables to Power Query.

Afterward, right-click on the Queries pane area >> choose the New Query option >> Other Sources option >> Blank Query option.

So, a new query will be created.
Now, rename it as “Merge All Values”.

Now, click on the Merge All Values query and insert the following formula to compare two tables and merge values through the power query.

=List.Union({Term_1[Name],Term_2[Name]},Comparer.OrdinalIgnoreCase)

Subsequently, hit the Enter key.

Thus, you have compared and merged two tables and got the students who passed at least one term.
Now, to get the IDs, go to the Query Settings pane on the right side >> APPLIED STEPS group >> right-click on the Source option >> choose the Insert Step After option.

As a result, a new step will be added.
Following, rename it as “Insert IDs”.

At this time, click on the Insert IDs step and insert the following formula in the formula bar.

=Table.SelectRows(Students,eachList.ContainsAny({[Name]},Source))

Subsequently, hit the Enter key.

As a result, you will get the student ids for the merged values respectively.

Thus, you have compared and merged two tables in Power Query successfully and the final outcome should look like this.

2. Compare Two Tables with Power Query and Find the Common Values

Now, you might need to compare two tables and find the common values of the tables. Say, you need to find the students who passed in both terms. Follow the steps below to compare two tables through power query and find the common values.

📌 Steps:

First, follow the first 16 procedures from the previous example to prepare tables for the power query and to create a new query named “Find Common Values”.

Afterward, click on the Find Common Values query and write the following formula in the formula bar.

=List.Intersect({Term_1[Name],Term_2[Name]},Comparer.OrdinalIgnoreCase )

Subsequently, press the Enter key.

Thus, you have got the students who passed in both terms.
Now, to add their ids, go to the Query Settings pane on the right side >> APPLIED STEPS group >> right-click on the Source option >> choose the Insert Step After option.

Consequently, a new step has been created.
Following, rename it as “Insert IDs”.
Now, click on the Insert Ids step >> insert the following formula in the formula bar >> press the Enter key.

=Table.SelectRows(Students,each List.ContainsAny({[Name]},Source))

Thus, you will be able to import the respective students’ ids.

Finally, you will get your desired output by comparing two tables through the power query. And, the final result would look like this.

Conclusion

So, in this article, I have shown you 2 practical examples to compare two tables through power query in Excel. You can also download our free workbook here to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day!