Turbocharge your trial balance with Power Queryby
The humble trial balance is at the heart of any accounts model, but with a few simple steps in Power Query it could work a lot harder for you.
In this tutorial I’m going to set out a few ways you can use simple Power Query functions to transform your management or statutory reporting data. The worked examples here are for an imaginary set of charity accounts produced using Xero, but the same principles apply regardless of the accounting system or the end product.
This tutorial is aimed at accountants who use Excel to produce financial information but have never used Power Query. If you want to use the same example as this tutorial, you can download it here.
Dealing with multiple tiers of information
Trial balances are struck at nominal level, like this:
However, all but the most basic accounting systems allow you further ways to categorise your expenditure for reporting. For example, Xero has tracking categories, Sage 50 has departments, and more sophisticated accounting systems allow you several tiers of data classification.
So let’s suppose, for the example above, you’re using tracking or departments to record your sources of funding (a requirement of SORP reporting). Every transaction can be assigned a funding codes as well as a nominal code. And if you were to run a transaction report, it would perhaps look something like this:
But how can you incorporate the funding information from the last column into your trial balance so that you can do further reporting? It is possible to run several trial balances with filters for each funding category, but this requires running multiple reports thus increasing the chances of error. Also, it’s unlikely that the individual trial balances will balance, as departmental/tracking information is not always captured through the balance sheet.
You can get round this in “normal” Excel with multiple sheets, lookups and subtractions but it’s easier and more flexible to create your own trial balance from one transactional report using Power Query.
Selecting the right transactional report
Firstly, select the report from the accounting system that you are going to use. You want a nominal transaction report that (a) gives a complete transaction listing (b) has separate columns for different tiers of reporting (c) balances to zero. Make sure you check this before you start building accounts queries and models!
Secondly, work out how your query will connect to the raw data. To keep this example as universal as possible, I have downloaded my report into an Excel workbook. However, it is possible for Power Query to connect directly into many accounting systems.
Getting started with Power Query
Save and close your accounts transactions report, having noted where it is, and open a new workbook. This new workbook will be where your query lives.
Locating Power Query depends on what version of Excel you have. In 2010 and 2013, you have to download it, and it appears as a separate tab. In 2016 it is called Get and Transform; in 2019 onwards you can find it as part of the Data tab. Here’s mine, from Office 365:
As our data has been saved as a workbook, select ‘from file’, “from workbook” and then navigate to the document. Selecting “import” will bring up a new navigation window, which will list all sheets and objects in the workbook.
In this case, there is only one, a sheet called “transactions”. Select this, and in the bottom right of this window, go to “Transform Data”. This will take you into the Power Query editor.
Navigating around Power Query
In the middle of the screen you’ll see something that looks much like the workbook, including the report headers. This is a preview window, and will not necessarily show you all the rows. On the left is a listing of all the queries in the workbook – only one at the moment, and you can collapse this pane for now.
On the right is the really exciting part – the Query Settings pane. Underneath the query name is “Applied Steps”, which shows the different transformations that have happened.
Amazingly, four transformations have already taken place, even though you’ve only done one thing. You can navigate back through the steps by selecting them to see visually what has happened to the data, and wherever there is a settings wheel (as there is with three of the four steps here) you can go into this to see exactly what options were selected.
So what are these steps? Source is where you selected your source file, and Navigation is where you selected the part of it that you wanted. Power Query has then tried to start organising your data. It’s decided that the Report title should be in the header row (Promote Headers and has tried to identify the type of your data (Change Type).
Let’s now fix this by deleting the last two steps (use the cross signal next to where it says Changed Type and repeat this for Promoted Headers)
Now we’ll remove the four rows from the top but leave the header row in place:
You can find the Remove Rows button on the Home tab. Selecting the arrow next to it gives further options. Select Remove Top Rows and in the next window specify 4 and press OK. Then select the Use First Row as Headers option (a few icons to the right of Remove Rows)
Once again, looking at the Applied Steps, you can see that Power Query has tried to be helpful and identify your data types, as well as logging the two transformations that you have done. There is now a little calendar next to Date, an alphabet next to Source and Description (indicating text), a 1.2 next to Debit and Credit (indicating a decimal number) and a 123 icon next to Account Code, indicating a whole number.
In my original file, the Account code was formatted as text. Let’s say I want this to continue. Highlight the Account Code column, and go to the Data Type indicator (highlighted). Select Text. It will ask you if you want to change the existing conversion or add it as a new conversion. In this case it really doesn’t matter, so select either option.
Data types are important in Power Query, as they affect the type of transformations you can do and the types of errors you might get. For example, if you right click on the Date column and select Transform, you get various date related transformations – you can instantly change the data to the end of the month, or to the day of the week. Whereas if you do the same for the Source column, you will get text transformations, such as UPPERCASE.
One final note on data types. In later versions of Power Query there are visual clues if something isn’t right with your data. Underneath the column headers, there are green lines under most of the columns, but underneath the Debit and Credit columns the line is partially black. This tells us that there are blank cells in this column. This can cause issues. In this case I can see that the blank cells (where it says “Null”) could be replaced with zeros.
To fix this, select both the Debit and Credit Columns (hold down Ctrl while you select both), right click and select Replace Values. In the following window, type “null” in the Value to find prompt and “0” (zero) in the Replace With box. Click OK, and you should see that all your nulls have gone, and that you have a clear green bar under the Debit and Credit columns.
Adding and removing columns
Let’s create a “net” column. With both Debit and Credit columns highlighted again, select the Add column tab, and then the Standard icon set (which gives a number of arithmetical options), and select subtract.
This will create a new column (on the end) called Subtraction and a new step called Insert Subtraction. Note – if you had not fixed the null issue in the previous step, you would get errors here.
Now is a good point to flag that you can not only rename columns, but you can also rename query steps so that it’s easy to see what you were doing. Rename the Subtraction column to Net by right clicking in the column header and selecting Rename.
You can now get rid of your Debit and Credit columns as you don’t need them anymore. In fact, let’s remove everything except the last four columns (Account Code, Account Description, Funding Line and your newly created Net column). Right clicking on a column, or selection of columns, gives you the option to delete the highlighted column(s) or delete everything except the highlighted column(s). Whichever way you do it, you should end up with something like this:
To turn this into our trial balance, we’re going to merge the first three columns, then group the data.
Highlight the three columns, and on the Transform tab, select Merge columns. This window asks you to select a separator and a name for the column.
I’ve chosen a custom separator of two dashes (“- -”) because I want to make sure this separator isn’t already in the data, so that if I later want to unmerge the data I can do it cleanly.
Next, we’re going to use the Group function (in the Transform tab)
In this example, I group by the column currently called Merged and sum the results of Net. The screenshot below shows how to do this.
Finally, let’s split out the funding code from the accounting code. To the left of the Merge Column option is the Split Column option. Again, this gives you various options for which separators to use (they are called delimiters in this window though!)
Using the custom separator I created (“- -“) I have selected the right-most, because I only want to split out the Fund name. (I want to keep the account code and description together for now).
Here is the final result in the Power Query window; I have renamed the two new columns and sorted by account code:
Close and Load
Finally, to exit Power Query and review your results, click Close and Load in the Home tab (top left hand corner). This will return the results of your Query, as an Excel Table. You can see that we have a trial balance with an additional layer of analysis – if you compare this to the original trial balance, the donations nominal (280) has now been split into two funds.
Don’t forget to save your workbook!
We’ve barely scratched the surface of all the things Power Query can do, but this exercise shows you why it can be powerful. Transformations that would be tricky in Excel can be done with a couple of clicks in Power Query. We have made the data more useful, without editing the original transaction file. And best of all, when the data changes, you can just refresh the query and everything will update.
In my next tutorial, I will look at further transformations, bringing in opening balances and explore mappings and conditional columns to create further accounts analysis columns.