How to Reconcile Accounts using Power Query

How to Reconcile Accounts using Power Query! What? Did I hear you right?

Reconciling accounts is a common task carried out by accountants on a regular and reoccurring basis. Creditor statements and bank accounts are examples of accounts that need regular reconciliation.

But what if you could create a model, within minutes, that you could use over and over for these reconciliations? Imagine the time it would save.

With Power Query you can and in this article, you will learn how to reconcile accounts using Power Query and then how you can use that query repeatedly saving you hours and hours.

Consider the following sets of records

The table on the left holds the stock count as per the system records. The table on the right holds the count from the physical stock count. We wish to reconcile these records and prepare a template that can be used on future stock count reconciliations.

Step 1: Load tables into Power Query

To load the tables into Power Query, select the first table
and from the Data ribbon select From Table/Range.

The query editor window will open, and the query will be set
up. The name of the query will be the
same as that of the table.

From the Home Ribbon select Close & Load and Load to. From the Import Data window, select Only Create Connection. Only Create a Connection will not load the tables to either Excel or Power Pivot. Instead, a virtual table is set up.

You will see the Queries and Connections on the right of
your screen.

Repeat the same steps to also load the Stock Count table as a connection via Power Query.

Step 2 – Create a table of unique stock Items

By hovering over the queries shown in the Queries & Connections
pane, you can access the Reference feature.

Referencing a query will take a query at its final
transformation step and use that as the starting point for a new query. Once selected the Power query window will open. A new query with the same name as the original
query and a reference number will be set up.

Under Properties, we can change the name of this query to Reconciliation.

Next, select Append Queries from the Home ribbon in Power Query. From the drop-down, select the table you did not use as the reference table, in this case, the stock_count table.

It is worth noting here, for append tables to work in this case, we needed both tables from which we are trying to create a unique list, to have the same column header. In this case, the column header is Stock Item.

Select the Stock Item column and from the Home Ribbon Select
Remove Columns and select Remove Other Columns.
This leaves us with one column containing Stock codes. However, this table contains duplicates and
we need to remove these.

Step 3: Merge Tables into reconciliation
table

To merge the tables into the Reconciliation query, first, ensure you are working with the Reconciliation query. Next, select Merge Queries from the Home ribbon.

Select the Stock Item column from the Reconciliation. Then select a table you wish to Merge with and select the matching column. In this case, it is the Stock Item column. This tells power query which columns to match when merging.

This will add a new column to the table. Each cell of this column contains a table of data. By right clicking on the white space in any of these cells, you can get a preview of the table content. From the preview available we can see the table contains a Stock item column and a Qty column. We need the Qty column.

To expand the table, select the expand icon beside the
column name. As we only need the Qty column
we can unselect all other columns.

We will keep Use original column name as prefix selected as
we wish to be able to identify which table the quantity comes from.

The result will be a new column containing the quantity from
the selected table. There will also be
some null values as the system record may have shown stock items that were not located
in the stock count.

Carry out the same steps again to merge the remaining table into the Reconciliation table

The result will be a fully combined table with all stock codes
and counts from both the system records and the physical sock count.

Step 4- Create Custom Column to calculate difference

Before we add a custom column to calculate the difference between
the Stock count and the System record, we must remove any nulls. The reason for removing the nulls is that
when we create a custom column, this will be seen as a blank and we need it to
be seen as a zero.

To replace the nulls with 0’s select we must first select
both quantity columns. From the
Transform ribbon select Replace Values.

In the Value To Find field we enter ‘null’ and Replace With
we enter ‘0’. This will update the table
and replace all nulls with 0’s.

We can now add our Custom column. From the Add Column ribbon select Custom
Column.

Rename the Column to Difference.

Now we can enter our formula to our custom column. On the right, you have Available columns. These are the columns in the table you selected. Remember These formula work of columns and not cells like Excel.

From the Available columns, select Stock_count.qty and press
insert. The custom formula will update with
that column. To finish the formula we can
then take away the Stock system qty.

Step 5 – Create conditional Column to
record status

The final column we will add to this table is a column to
identify the status of that stock item in the reconciliation. For example, if the stock item has not been
found in the Stock_count qty column, then this column will be = 0 then. If the stock count and the system record are
the same, then the item is reconciled. If the stock record count is 0 then the
item was not found in the stock records.

To create columns based on IF statement we add Conditional
Columns. To insert a conditional column,
from the Add Column ribbon select Conditional Columns.

First name the column, we can then set up the if else statement as shown in the GIF.

Step 6 – Insert Pivot table and carry out reconciliation

Now that we
have finished working on our query and we have made transformations that
include appending, merging, custom columns and conditional columns, we are now
ready to carry out our reconciliation.

From the Home ribbon select Close & Load and select Close and Load to. In the Import Data window, select PivotTable Report. For this example, I am going to place the table on the same worksheet

From the
PivotTable Fields, add the Stock item to the Rows. In the values add the Stock system qty, the
difference and the Stock system qty.

Once we have
set up the pivot table, we must confirm the Grand totals match and balance. We can do this by check quickly summing the quantities
on both tables and getting the difference.

For a final touch, I have added a slicer so the user can filter the table accordingly. However, you could also add the status to the filter field of the pivot table.

Step 6 – Reuse Query

You can reuse this query over and over without the need to carry out the transformation steps or set up again. First, make sure you save your file. Once you have structured your data in the same way as the original tables, reusing the query is very simple.

When you carry out the next stock count, all you must do is to replace the old data within the tables with the new data. Do not delete the tables. Instead, copy the data from the new source and paste it over the original data.

Finally, from the Data Ribbon Select Refresh All and your entire reconciliation will update.

Automating Bank Reconciliations with Power Query

And it’s that
simple! That’s how we reconcile accounts using Excels Power Query. I really hope that you understood all of that
because this next example is a little more complex.

In this video, we are going to reconcile a Bank statement against the company’s records. It would be common for an organization to download their bank statement in Excel or CSV format. It is also common to have the ability to download reports from the nominal ledger.

In this example, we have the bank statement and nominal ledger set up as tables in Excel. We will connect to these tables, carry out the reconciliation and then show you how you can quickly change the source and reuse this query over and over every time you need to reconcile the bank.

Make sure you stay tuned to the end of the video because there is a Steem Learn and Earn Activity and full instructions will be given in the video.

Learn and
Earn Activity

In the video example, we assumed there were no outstanding items from the previous period. However, let’s face it, most often that is not the case. Especially with bank reconciliations.

Download the attached file. It contains 3 worksheets. An opening reconciliation, the bank statement, and the nominal ledger. Prepare a model to reconcile the accounts. None of the tables have been loaded to Power query so you are starting this reconciliation from scratch.

In the comments section below detail the steps you took to carry out this reconciliation and create the model. Keep in mind this reconciliation is different to above as there is an opening reconciliation to take into consideration.