Power Query – building in checks and controls – part 2

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 this part of the series, we will try and identify some possible causes of our difference and see if we can create the queries necessary to reconcile our totals.

What could possibly go wrong?

Our example is very simple and there is really only one significant operation involved: the merging of our two tables using the Product code as the key field in both tables. When merging tables, it is possible to choose different ‘join kinds’. These control what will happen if all the records in one table are not matched with the other table. When we set our merge up, we accepted the default join type of ‘Left Outer(all from first matching from second)’. As our first table was our Product category table, this will include all the rows from that table and only the rows from the Product sales table where there is a match. That means that, if no product category has been allocated to a particular product code, the sales for that product code will not be included in our output table. We can see this in our example. Code B002 has been omitted from our category table so the sales value of 390 for this product is not included in our output table:

Although this might be the most obvious reason why our two totals don’t agree, it is not the only potential cause. If our product code is included more than once in the category table, duplicate rows will be included in our output table:

The importance of Join Kinds

In our example, we assumed that we wanted to add our sales total to our categories table, so we started with the categories table in our merge and left the Join Kind at its default ‘Left Outer’ value. Often when merging queries, one query will be intended to have many matches for each key value in the other table: for example if were we merging invoice transactions, rather than totals, with a client file. However, for our example to work correctly, each product code should be included once, and once only, in our Product Category table. Accordingly, it’s a good idea to build in checks to identify any issues in our source tables.

There are specific join kinds that identify values that are missing from one of the tables. The Left Anti and Right Anti joins include those rows that are only in the first table or second table respectively. So, to create our check for product codes missing from our Product categories table, we could make a copy of our existing merge query by right-clicking and choosing Duplicate and then click on the gear icon next to the Source step to change how our merge operation works. Here, we have changed the Join Kind to Right Anti in order to only include rows where the product code exists in our second, ProductSales, table and not in our ProductCategories table:

We can then expand our ProductSales column to include the Product code and Sales value for use in our reconciliation.

Keep Duplicates

We also need to check for duplicated rows in our output table. To do this, we can reference our SalesByCategory output table and select our Product code column and then from the Home Ribbon tab, Reduce Rows group, choose Keep Rows, Keep Duplicates:

Next time

Having created the queries that identify some of the possible differences between our input and output totals, in the next part of the series we are going to build our reconciliation.