Compare Tables in Excel & Power BI

Do you often find yourself in dire need to compare two tables in Excel? In today’s blog post, we will create an Excel workbook that compares two tables and highlights the changes in a third table. You can load new versions of the tables, click Refresh All in Excel, and immediately see the changes in the third table.

The solution is 100% free of code!!!

• No VBA
• No Formulas
• No need to apply Power Query M
• Whenever the tables change, click Refresh All in Data tab, and you’ll find the difference between the tables
• Solution can also be used in Power BI

To follow this tutorial, and create an automated comparison of two tables, we will use Power Query, AKA Get & Transform in Excel 2016. You can follow the steps below with Excel 2010 and 2013 after installing Power Query Add-In, or in Power BI Desktop.

Background

This is not the first blog post that will show you how to compare two tables. As an advanced Power Query user, you can check out Chris Webb’s blog posts here and here . In this blog post, I tried to tackle the problem in easy to follow steps – without any prior knowledge of Power Query or VBA – while allowing you to quickly find the differences between the two tables.

Want to download the solution, and skip this tutorial?

As a subscriber of DataChant, you can download the Excel workbook with the subscriber’s password here. If you forgot the password, or have never subscribed to DataChant, fill the form below, and you’ll get the password by email.

[rad_rapidology_inline optin_id=optin_5]

Want the full solution?

You can now purchase the full solution (in a ridiculously absurd price). The full solution includes the following features:

1. Compare the tables and detect extra rows
2. The queries will not fail on refresh when column names change
3. Includes a Power BI Report version (.pbix), Isn’t it cool? You can now import any two tables to Power BI Report and show the different values in a Matrix visual

[purchase_link id=”8022″ text=”Buy Now” style=”button” color=”blue”]

Let’s Start

Download this starter workbook to follow the tutorial.

For each of the two tables click From Table, or From Table/Range in Get & Transform section of the Data tab or Power Query tab (depending on your version of Excel). You can import the two tables from any other supported external data sources. You can also use Power BI Desktop and use Get Data button to import to two tables.

Not sure how to import your tables to Excel? Follow this article.

Enter the Query Editor (Not sure how? Click Show Queries or Queries & Connections in Data tab of Excel, or Power Query tab in Excel 2010/2013, and double click on one of the queries that you loaded. If you use Power BI Desktop instead of Excel, click Edit Queries.

Select Table1 (or the first query), and click the drop down menu of Index Column in Add Column tab. Then, select From 1.

Note: in some of the screenshots below you will see that the index starts with 0 instead of 1. Please ignore this subtle change in the screenshots. Sorry for the confusion.

Rename the new column to Row. This column will help us to identify each row in the table. Not sure how to rename a column? Just double click on the text of the column’s header and change the text.

If your two tables include empty cells, you will need to perform the following step –

Select all columns (By pressing CTRL key as you click all the column headers). Then right-click on one of the headers, and select Replace Values in the context menu.

In Replace Values dialog, set null as Value To Find, and click OK.

Right-click on the header of column Row, and click Unpivot Other Columns. This is a magical step in Excel & Power BI. If you are not sure why, read my other articles here.

Let’s rename the Attribute column to Column. now you can see that the last unpivot step rearranged our table in the format of Row, Column, and Value. The last column includes all the values in our original table, with the row & column indexes in the corresponding columns.

Rename the header Value to Table1 Value, and select the query Table2 (or the second table you imported).

Repeat the steps above in Table2:

1. Add an Index Column (That starts with One).
2. Select all columns and replace nulls.
3. Select the index and apply Unpivot Other Columns.
4. Follow the renaming of headers, with one exception – This time, rename the third column to Table2 Value.

When you are done, here is the preview of Table2:

Merging the tables

Select Table1, click Home tab, and click Merge Queries as New in Merge Queries drop-down menu.

In the Merge window, ensure that Table1 is selected in the first drop-down menu, and select Table2 in the second drop-down menu.

Click on the header of the column Row, and then, while the CTRL key is being pressed, click on the header of column Column. Repeat the steps on Row and Column of Table2. Ensure you see the number 1 on the header of Row, and the number 2 on the header of Column on both tables. When you are done, ensure the Join Kind drop-down menu is set to Left Outer, and click OK.

Let’s click the expand icon in the header of NewColumn.

In the expand pane, uncheck all fields, and check Table2 Value. Uncheck the box Use original column name as prefix, and click OK.

We now have a single table with the values of table1 in Table1 Value, and the values of table2 in Table2 Value. In the next step, we will keep all the rows where the values are different.

In Add Column tab, click Condition Column.

In Add Conditional Column window, select Table1 Value as Column Name.

In the Value drop-down menu (with the ABC 123 icon) click on Select a column.

In the next drop-down menu, click Table2 Value.

In the text box after then, set 0 as Output, and set 1 as Otherwise. When you are done, click OK.

Following the last step, we now have the value 1 in Custom, whenever the values in a specific pair of row and column in the two tables are different.

Let’s merge between Table1 Value and Table2 Value in a new column. The format in the new column will be as follows:

Value1 –> Value2

Go to Add Column tab. Select the columns Table1 Value and Table2 Value, and click Merge Columns.

In the Merge Columns dialog, select –Custom– as Separator.

Set the text “ –> ” in the Separator box (without the quotes). Then, set Change as the New column name (optional), and click OK.

Now, let’s add a second conditional column. In Add Column tab, click Condition Column. The new column will contain the original value from Table1, if the values in both tables are equal, or the new merged value: Value1 –> Value2 when the values are different.

In the Add Conditional Column dialog, select Custom as Column Name, set 0 as Value, and click Select a column in the drop-down menu below Output.

In the second Output drop down menu, select Table1 Value.

In the drop-down menu below the text Otherwise, click Select a column.

In the second drop-down menu, select Change as the column, and click OK.

We can now remove the columns: Table1 Value, Table2 Value, Custom and Change, and rename Custom.1 to Change.

Re-Arranging the Rows and Columns

In the final step of this tutorial, we will rearrange the table, so the values in Column will be used as headers in the new table. This step is performed using the Pivot transformation which negates the Unpivot we had performed earlier.

Select the 2nd column (Column), and click Pivot Column in Transform tab.

In Pivot Column dialog, click the drop-down menu of Values Column, and select Change.

Next, expand Advanced options, select Don’t Aggregate as Aggregate Value Function, and click OK.

We are almost done. Let’s filter all the rows that have no changes. You can skip this step if you want to see rows with 100% match.

For each of the columns (except the column Row), change the column type to Text. To change the type, click on the ABC123 icon in the header of the column, and select Text in the drop-down menu.

Click on the filter button of Product. Select Text Filters and click Contains.

In Filter Rows dialog, select Product as the first column, and set ” –> ” as Value (without the quotes). Select Or in the next line, select Date as the second column, and set ” –> ” as Value.

Now, click Add Clause, Select Or in the third line, select Amount as the third column, and set ” –> ” as Value. Before you click OK, ensure that your dialog is set as this screenshot:

That’s it. We are ready. In the last step, we filtered out all the matching rows between Table1 and Table2. We can now click Close & Load and load the new table into the worksheet.

And voila! here is the result:

Hope you enjoyed this tutorial. I am sure that from now on, you will have fun when you need to compare tables.

Subscribe to DataChant and download the solution workbook from here. You don’t need to subscribe again if you already have the password.