Power Query – building in checks and controls – part 3

Introduction

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:

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:

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:

=[Total]*(([Count]-1)/[Count])

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

=SUM(Table_DuplicatedProductCodes[DuplicatedValues])

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:

Conclusion

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.