ICAEW.com works better with JavaScript enabled.

Power Query – building in checks and controls – part 3

Author: Simon Hurst

Published: 27 Apr 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

In this three-part mini-series we are going to see how Power Query can help identify potential problems in our output tables. In the first part we looked at the creation of check totals from the original file and the output table and in part 2 we created the queries needed to help reconcile our two totals. In this final part, we will build our reconciliation using our totals and exception queries.


Our simple example combines two input tables to add the category from one table to the other. Our check totals show a difference between the total of the all the values in our input file and the total of the corresponding column in our output table. In the second part of the series, we created the queries that listed differences between our input and output tables. We will conclude the series by building these queries, and the total query we created in part one, into a reconciliation between our two totals.

Back to the beginning

To best see what is going on, we will include our reconciliation on the same sheet as our input and output tables. Our aim is to reconcile the sales total directly from our input file with the total from our output table, so we will start with our single cell ProductSalesTotal query and load it, as a Table, into cell K2 of our worksheet. We can use the Table Design Ribbon tab, Table Style Options group, Header Row check box to turn off the header row and just display our total value:

Excel spreadsheet screenshot

We have loaded our MissingCategories query to a table on its own worksheet and can use SUM() to include the total of all missing items in cell K4. For the purpose of our reconciliation, we need to subtract it from our input total:

=-SUM(Table_MissingCategories[Sales total])

We will do the same for our DuplicatedProductCodes query, but first we need to add a column to that query to calculate the total value of the duplicated rows. Firstly, we will use the Transform Ribbon tab, Table group, Group By command to group our rows by Product Code and count the number of times each code appears and sum the value of the Sales total column:

Excel spreadsheet screenshot

For our reconciliation, we don’t want the full total for each Product code because a single row is included correctly. Accordingly, we will use the Add Column Ribbon tab, General group, Custom Column command to enter a formula to calculate only the repeated amount, based on including one row less than the total number of rows:


Excel spreadsheet screenshot

We can now add our duplicate rows total to our reconciliation in the same way that we added our missing items:


To complete our reconciliation, we have added a total for our input total and our reconciliation items, and created a simple total of the values in the Sales total column of our output table:

=SUM(Table_SalesByCategory[Sales total])

We have formatted our totals to help indicate which tables they come from and added a Difference calculation to highlight any additional issues that our existing checks have not discovered. Finally, we have used the Insert Ribbon tab, Links group, Link command to add hyperlinks to the exception report tables used in the reconciliation to help the users follow the trail to find and resolve the issues:

Excel spreadsheet screenshot


We have seen how easy it is to create exception reports using Power Query to highlight predictable errors. In addition, building in check totals, particularly those that compare what we started with and our final result, can help reveal any issues in our source data and the processes that we have applied to it.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.