Slow to Fast 3

This is the final post in the Slow to Fast series, where we are incrementally improving the efficiency of reconciliations.

In the first post, we looked at conditional formatting. This was a great option when the lists were relatively short, stored on the same worksheet, and only required us to look at a single column.

In the second post, we examined lookup functions. This enabled us to quickly reconcile lists that were on separate sheets and that required us to compare the amount. But, if we perform such a task on a recurring basis, we can consider using Power Query. Before we jump into the details of Power Query, let’s confirm our objective.

Objective

Our check register is stored in a table called check_register:

Our bank data is stored in a table named bank_activity:

We want to compare these lists. Specifically, we want to find out which checks appear on both lists (which have cleared the bank) and which checks do not appear on the bank activity (are outstanding). Let’s see how Power Query can help.

Video

Details

We’ll use these steps:

• Retrieve tables
• Cleared checks
• Outstanding checks

Let’s get to it.

Retrieve tables

The first step is to retrieve both tables with Power Query. In this illustration, both tables are inside Excel already. But, Power Query is able to retrieve data from many different data sources if needed.

We select the check register table, and then click the Data > Get Data > From Other Sources > From Table/Range command. We see a preview of the table in the Power Query editor, like this:

We then click the bottom half of the Close & Load command, and pick Close & Load To. In the resulting dialog, we opt to Only Create Connection:

Next, we do the same thing for the bank activity. Data > Get Data > From Other Sources > From Table/Range. From the resulting Power Query editor, we Close & Load To. Only Create Connection.

We now have access to both the check_register and bank_activity data, as shown in the Queries & Connections pane:

With that complete, it is time to generate our list of cleared checks (those that appear on both lists).

Cleared checks

The checks that appear on both lists have cleared the bank. To create a list of the cleared checks, we’ll compare these two lists. To do so, we select Data > Get Data > Combine Queries > Merge.

In the resulting Merge dialog, we pick check_register from the top drop-down and bank_activity from the bottom drop-down. We also need to click the Check column in both the top and bottom tables. Finally, we need to select Inner (only matching rows) from the Join Kind drop-down:

We hit OK and can see this in the editor:

We delete the selected bank_activity column by pressing the Delete key on our keyboard, or by using the Home > Manage Columns > Remove Columns command.

Once complete, we Close & Load To a Table:

We hit OK, and bam:

Next we need to create a list of checks that appear on the check register but NOT the bank activity.

Outstanding checks

The list of oustanding checks (those on the check register but not the bank activity) is created in a very similar way, with only one little twist.

We begin by using the Data > Get Data > Combine Queries > Merge. In the resulting dialog, we once again select check_register and then bank_activity. We also click the Check column in both lists.

Now, here is the little twist. From the Join Kind drop-down, we select Left Anti (rows only in first).

We click OK and see the following preview:

Once again we delete the bank_activity column, and then Close & Load To a Table. Bam:

If desired, we could quickly compute the total of outstanding checks by using the Table > Total Row checkbox.

Conclusion

Now, the really cool part of using Power Query is that it is easy to refresh in future periods. We can open the workbook and use Data > Refresh All to update the reconciliation.

I hope that the three features discussed during this series will help you get your list comparisons and reconciliations done more quickly 🙂

Sample File