Compare Tables in Excel and Power BI

Do you often find yourself in dire need to compare two tables in Excel or Power BI? In this tutorial, and in this follow-up, we created 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.

Buy Now

You can follow this tutorial, or save your time, and purchase the full solution:

  • Compare the tables, and detect extra / missing rows
  • Will not fail on refresh when column names change
  • Includes a Power BI Report version (.pbix)
  • Includes Version 2 that compares tables by matching keys.

The Excel version highlights the differences between Table1 and Table2 in the third table, as shown in this screenshot.

If you have extra rows in one of the tables, you can find them as well.

Compare Tables v2

This bundle contains a new method to compare between tables with matching keys. You can use the function which was shared in this tutorial, or buy the full solution here.

Minimal Requirements

Excel 2010 or 2013 with latest Power Query installed, or Excel 2016, or latest Power BI Desktop.

Instructions

  • Open the Excel workbook, and click Refresh All in Data tab to apply the comparison between tables.
  • Unzip the Power BI Report, and click Refresh to apply the comparison between tables.
  • Open Query Editor to rewire the queries to your own tables. Not sure how? Follow this tutorial first.
  • Learn how to use FnCompareTables in CompareTables 2.xlsx. Read more here.
Buy Now