Content seriesView full content series
Turbocharge your trial balance with Power Query, part 3by
In the final part in this Excel tutorial series, Jessica Slack demonstrates how you can use some of Power Query’s transformation tools to produce management accounts.
In the first two parts of this tutorial, we brought departmental information into the trial balance, and then used a couple of different techniques, conditional columns and Merge Query, to add further analysis information.
Up to this point, we’ve been looking at creating an enhanced trial balance for statutory accounts. In this last part, we will use similar data to explore how we can combine budgeting and actual data to create a data model to produce management accounts, and we will meet some of Power Query’s most useful transformation tools along the way.
This tutorial builds on the example in part 1 and part 2 but could be worked independently if you already have prior Power Query knowledge. To follow the examples in this tutorial, please download the files for part 3.
Comparing budget information to actuals using Power Query
To demonstrate an approach to this, I’ll use a cleansed version of the transaction listing from the first tutorial and an extract from a budget template. These are tabs 1 and 2 respectively in the download file. You can see that they are not in a consistent format to start with.
In order to combine them, we need to create a query from each of these, transform them both until they are in almost the same format, and then bring them together into one data table.
In a new blank workbook, first import the transactions table as a query. (This is a tidied version of the original transaction listing). Rename this query 'actuals'. Then, import tab 2 (Budget) as a separate query. You don’t need to come out of the Power Query window to do this – in fact you can use the recent source option as a navigation shortcut:
With this new query, remove the last two automatically added steps and rename it to “Budget”. It’s fairly clear that this data needs a bit of tidying:
Transforming the budget data into a comparable file
One issue we have is that the budget/department line (LA Programme Funding) appears as part of the headers, and if we want to use this information in a comparison against actuals, we’ll need to bring that data into a column. There are various ways to approach this; the key things to consider are;
- What patterns can you see in the data that might inform the transformations you choose to use?
- How repeatable do you want the process to be?
In this case, I’m going to assume this is a standard budget template. I can also see that column 2 (account description) has null data wherever there is no transaction data. I can use this as a helper to extract the department name in row 3. So let’s create a conditional column where if the data in column 2 is null, then the conditional column returns whatever is in Column 1, otherwise it returns a null.
This creates a result like this:
You can then use the Fill Down function by right clicking on the column. Fill Down fills any empty cells with the value above and is an incredibly useful function for this sort of data tidying exercise.
This leaves you with a table that looks like this:
You can now remove the first four rows, promote the header row, and filter out any blanks in the first column, and you now have an organised table of data like this:
Unpivot – possibly Power Query’s greatest tool
In order to append this data to the actuals data, we need to make sure that the column headings are the same and have the same type of data in them. Most of the columns we’ve got here are consistent – Account Code and Account Description are fine, and the column currently called LA Programme Funding will be fine once we’ve renamed it to Funding Line to match the title of the Actuals column equivalent.
However, in the actuals data, we have our dates in one column, and in this example, the dates are along the top.
In order to sort this out, we need to highlight the two date columns and then use the excellent Unpivot column function, which can be found on the Transform tab.
Once this is clicked, the data is transformed into this, with a new row for each separate column:
Final steps before combining the data
Our data is now almost complete. Rename Attribute to Date (and change its type to Date as well) and LA programme Funding to Funding Line. You could now combine the data sets at this point but there are a couple of further steps to help with later data analysis.
Firstly, you might want to transform the Date column in both queries to end of month, so that you can more easily report by period. To do this, right click the column, select Transform and then End of Month. (As you can see, you can also transform your data using quarterly or weekly transformations, or using the period name or number – whatever suits your reporting best).
Secondly, I would suggest adding a Source column to both queries before combining them so that you can separate the data back into budget and actual and compare the two in your final analyses.
Starting with the Budget query, go to Add column, then add custom column.
This takes you into a dialogue box where you can do all sorts of exciting things with columns and formulas, but in this case we’re simply going to add the word Budget (including the quotation marks).
This will produce an additional column with the word Budget in every row. Rename this column to Source.
Do the same for the Actuals Query, except write ‘Actuals instead of Budget. Don’t forget to rename the column to Source.
Your Actuals Query should now have seven columns and your Budget Query should have six – there is a Description column in the Actuals that isn’t present in the Budget query. It doesn’t matter if you have extra columns in one query, as long as the column headings and types are exactly the same for the columns that do match.
Combining the data
You can now combine the data using Append Queries function, which is on the home tab. In this example, I was in the Budget query to start with, so the query to append is the Actuals one, but it doesn’t matter what order you do this in.
Hit OK. If everything is correct, then you should have seven columns in the final query. If you have more, then there is probably an error in the column heading – this is case sensitive so must be exactly the same!
Close and Load, and it should look something like this:
From there, it’s only a few quick clicks to creating an interactive pivot table, or you can use this output as the basis for a dynamic management accounts model, or as the base data for a Power BI file.
You can follow a similar process for bringing in prior year comparatives, and of course you can apply all the transformations we looked at in the first two parts of this tutorial to add further analysis columns to your data.
However you use the end result, Power Query allows you to do quick, repeatable and powerful transformations that make it easy to combine data in different formats.