Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook

You can download our practice workbook from here for free!


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.

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

Read More: How to Compare Two Pivot Tables in Excel (3 Suitable Examples)


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

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

Read More: How to Compare Two Tables in Excel Chart (5 Suitable Examples)


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!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo