In this guide, we're going to show you how to compare two tables using Get & Transform Data in Excel. Download Workbook In our example, there are two identical tables with the same column names. Although both tables contain the same data under the Name column, there are differences in the data under the Form column. For example, both tables contain “Charizard”, while the left table has “Mega X” and the right one has “Mega Y”. We can join these two tables to compare them by using Get & Transform Data. Get & Transform Data feature consists of tools previously known as Power Query. You can get more information about this here: Power Query 101. Creating queries from tablesFirst step is to convert the tables into Power Query structure.
Joining queries to compare two tables using Get & Transform DataNext step is to join (merge) the queries in a new table. This will allow us to see the differences between tables.
After populating the merged table, you can easily find any rows containing different data. The rows that contain the same data in the specified columns are listed in the same column. Empty columns indicate rows with different data. |