Download the sample Power Query workbook
CMBI developed this Power Query workbook using Excel 2016. If you are using Excel 2010 or 2013 you will need the Power Query Add In which is FREE from Microsoft.
- Click here to download Excel worksheet compare Power Query Sample Workbook
- Click here to download test files for the Power Query Sample Workbook
Introduction to Power Query worksheet comparison tool
We built this spreadsheet comparison/audit tool using Power Query. It compares data from two Excel workbooks and reports any differences in cell values (although not formulas) between equivalent cells in the different workbooks.
For instance, if the value for Sheet1 Cell A1 in Workbook A = 15, and the value for Sheet1 Cell A1 in Workbook B = 15.001, it will report the two cells as having different values.
The workbook will compare tens of thousands of cell values instantly, reporting all differences
Instructions to use the sample
The Power Query workbook contains instructions and explanation of the main Power Query features used in the example.
Download the workbook from the links above and get started comparing your workbooks.
Power Query worksheet compare utility advantages
Some advantage of this Power Query worksheet comparisons tool include
- No VBA, macros or security concerns
- All native Excel functionality
- Easily extensible and customisable - for instance to compare a folder of workbooks
Power Query functions used in the sample
Power Query functions used in the sample include
- Excel.Workbook
- Table.AddIndexColumn
- Table.UnpivotOtherColumns
- Table.ReplaceErrorValues
- Table.FillDown
- Table.SelectRows
- Table.ReplaceValue
- Table.AddColumn
- Table.RenameColumns
- Table.Sort
- Table.TransformColumns
- Table.NestedJoin
- Table.ExpandTableColumn
Want to learn more about Power Query?
Power Query is part of Microsoft Power BI. CMBI provide hosted and onsite training for all levels of Power BI users and developers. Please contact julie@cmbi.com.au to arrange a training sesssion or learn more about training and mentoring options with Power Pivot and other BI tools
About CMBI
CMBI designs and develops business intelligence and analytics solutions for clients in Sydney and regional NSW. CMBI technology expertise includes Power BI, Power Pivot, Excel, SQL Server, Analysis Services, SharePoint BI, and Tableau