Turbocharge your trial balance with Power Query, part 2by
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 the second part of this series, Jessica Slack explores categorisation columns.
In part 1 of this tutorial, we went through the process of adding departmental information to your trial balance. We’re now going to further extend the trial balance by adding additional categorisation columns using two different methods.
At the end of the last tutorial, we had produced a trial balance showing account codes split by funding lines. For charity reporting, we don’t always need to know the individual funds; sometimes we just need to know whether we’re looking at Unrestricted or Restricted funding. Let’s go back into the query and create a new column to show whether our funds are Unrestricted or Restricted, using the data in column B.
Figure 1 – trial balance split by funding types
To go into the query, you can either right click then select Table and then Edit Query, or you can use the Query tab in the top right.
Figure 2 – getting back into the query to edit it
Adding a conditional column
Once in the query window, go to Add Column, and you’ll see various options for creating a new column. In this case we’re going to use Conditional Column. This is like using an IF statement to create a new column from data that is present in other columns. In this case, we want to use the data in Fund column to create a new column, where the values will either be Restricted or Unrestricted – or blank if the original data was.
This is one of the most powerful transformation tools and it is pretty easy to follow the logic. The screenshot below shows the options that will give us the desired outcome:
Figure 3 – conditional columns
The second clause means that if there is a blank in the Fund column, there will be a blank in the newly created column.
Note that the dialogue box defaults to one condition, and to ‘equals’ as the operator, but it’s easy to add as many clauses as you want, and you can also change the operator, which is specific to data type. So in this case you could choose ‘contains’ or ‘begins with’ or other text-based options. If you had a date or number data type, you could select ‘between’ or ‘greater than’.
Close and Load the query, and your table now has an additional categorisation column.
Figure 4 – output with restricted/unrestricted column
Another way to add data – merge queries
While conditional columns offer lots of options for adding data, we can also import an entire chart of accounts using the Merge Queries function. This is the sort of thing you may have used a LOOKUP formula for in the past. The Power Query equivalent is easier to use and considerably less resource-intensive.
The Chart of Accounts that I am going to use is in the first tab of the workbook, is formatted as a table called COA, and looks like this:
Figure 5 – Chart of Accounts (COA)
The first step to combining queries is to bring in the new data as a new query. In the workbook with the existing trial balance query, let’s bring the new data in, using the same process as last time (Get Data – From File – From Workbook) and point it at the workbook with the chart of accounts data in it. This time, when you go to import, you will see three options, which are the two worksheets of the workbook (highlighted yellow) and the table (Circled in blue)
Figure 6 – import options
You can import the query from the worksheet (labelled ‘1 Chart of Accounts’), but it’s usually a lot simpler and neater to do it from the table (which is why I would recommend having source data in tables where possible). Highlight the COA table, press Transform Data, and you’ll see that you have a new query listed on the left underneath the old one. Because the data was already structured as a table, it has been easy for Power Query to identify the header row and organise the data.
Figure 7 – the new COA query, showing under the original one in the Queries panel on the left
In order to merge this data successfully with the transactional data, we need both queries to have a column in common. In this case we will use the Account Code. We need to do some tidying in both queries so that the data is consistent.
In the original data and in our first query (transactions), the account code was formatted as text, so let’s change the type in our new COA query back to text (right click on column, select Change Type). Then, jumping into the transactions query, which you can do by clicking on it in the Queries pane on the left, we need to isolate our account code again.
This is a repeat of what we did at the end of the last tutorial; highlighting the column, select 'split column', specify by delimiter, and the custom delimiter used.
Figure 8 – splitting out the account code
We could also do this by creating a ‘Column from examples’ (in the Add column tab) and then simply type 004 in the first line. Power Query will then guess what you want it to do, in the same way that Flashfill works in Excel.
If we use the first method, our query should now look like this (I have also changed the Account Code back to text):
Figure 9: results after account code split and after changing data type in the account code column
Now we are ready to merge our two queries. The option for this is on the Home tab:
Figure 10: Merge queries on the Home Tab
Whether you merge into the existing query (which we will do) or create a new merged query, the following dialogue is the same. It takes the query you are in as the first one, asks you to select a query to merge with. You also need to highlight the columns that both tables have in common. Finally, you need to select the type of join. It defaults to the one that you are probably most likely to use, which is the Vlookup equivalent – known as Left Outer.
This is what the dialogue window should look like with everything complete. You can tell it’s working, because at the bottom it has said it has matched 11 out of 11 records, ie every row of data in your original transactions table has a match.
Figure 11 – merge data dialogue
If you press OK, you get a slightly odd output:
Figure 12 – table view
You need to press the two arrows to the right of the column header, which will then bring up the expand table dialogue, which asks you to say which columns you want to bring in. In this example I have deselected the first two, and also de-selected the option at the bottom to use the original column name as prefix. If you then press OK, you should see that your query now has the three mappings columns from the COA query attached.
Figure 13 – expand table view
If you close and load your query, it will now look like this:
Figure 14 – final output with new columns
You now have a trial balance, complete with various mappings, ready for you to use pivot tables or SUMIFS to map into your accounts.
In the third and final part of this series, we will look at how we can further enhance this data by adding budget information and opening balances.