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.
To remove duplicate stock codes, select Remove Rows and Remove Duplicates.
A table with a unique list of all stock codes will be the end result.
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. Next select the 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 other columns can be unselected.
By using the original column name as prefix we will 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 a Custom Column to calculate the 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 a 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, we will name the column. The if-else statement can then be set up 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.
Online Training Courses
SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS & LEARN AND EARN ACTIVITIES TO YOUR INBOX
SIGN UP
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. To start this reconciliation you will need to load these tables into Power query.
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.
DOWNLOAD WORKBOOK NOW (NO EMAIL REQUIRED)
Next week I will post the solutions
Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox
Cross posted from my blog with SteemPress : https://theexcelclub.com/how-to-reconcile-accounts-using-power-query/