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.

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

Students' Records Dataset

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

Term Wise Students' Records to Compare Two Tables with Power Query

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.

Convert Range to Table to Compare with Power Query

  • 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.

Create Table Window

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

Students Record 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.

Rename the Table

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

Two Table to Compare with Power Query

  • 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.

Open the Power Query Window to Compare Two Tables

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

Students Table in the Power Query Window

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

Choose Close & Load To Option

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

Import Data Window

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

Tables to Compare in Power Query

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

Create a New Blank Query to Compare Two Tables in Power Query

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

Rename the Query

  • 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.

Formula to Compare Two Tables and Merge Values in Power Query

  • 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.

Query Settings Pane

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

Rename the Created Step

  • 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.

Formula to Show Respective Student IDs

  • 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.

Compared and Merged Two Tables in Power Query


2. Comparing Two Tables with Power Query and Finding 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”.

Create New Query to Compare and Intersect Two Tables in Power Query

  • 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.

Formula to Compare Two Tables and Find Common Values in Power Query

  • 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.

Query Settings Pane

  • 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.

Formula to Import 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.

Compared Two Tables and Found the Common Values in Power Query


Download Practice Workbook

You can download our practice workbook from here for free!


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. Have a nice day!


Related Articles


<< Go Back to Power Query Examples | Power Query Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo