Compare Tables in Excel & Power BI – Part 2

As a follow-up for the tutorial here, in today’s blog post, we will show you how to use an improved version of the Table Comparison implementation, and apply a comparison of rows with matching keys. The solution can be used for many purposes:

  • Audit repetitive changes on the same table
  • Create an automated revision control for your data
  • Automate regression tests in your reporting solution

Compare two tables in Excel

 

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.

Want to download the solution?

You can now purchase the full solution

Buy Now

Want to create the solution from scratch?

Create a query with the name FnCompareTables, and the following M expression (copy and paste it into the Advanced Editor):

Import your tables as two separate queries: Source1 and Source2.

Create a blank query and use the function as follows:

[single or multiple columns key] is a list of column headers, for example: { “ID1”, “ID2” }

[delimiter] is a text used to merge multiple columns. Don’t use a character that is already used in the headers. For example: If your multiple columns key is the combination of the columns Transaction-ID and Product-ID {“Transaction-ID”, “Product-ID”}, don’t use dash “-” as a delimiter.

Here are few examples that invoke FnCompareTables:

The example below can be used if you have multiple columns key as shown in screenshot below.

The example below can be used if you don’t have any unique IDs, and you want to ensure that the tables are matching by their row order. In this case, you can create the Index column and use it as the unique key.

The example below can be used if you have a unique key in a single column Transaction ID.

Next step is to load the query to a table and view the highlighted differences.

Matching rows (by multiple columns keys) will highlight the differences as shown in this screenshot:

 

Missing rows in the first table will be highlighted as follows:

Missing rows in the second table will be highlighted as follows:

Hope you find this tutorial helpful.

Subscribe to DataChant and download the solution workbook from here.

Subscribe To DataChant

Join our mailing list to receive exclusive content that was used in the preparation of this article.

Thank you for subscribing to DataChant. An email will be sent to you shortly with more details.

Leave a Reply