Multiple forecasts with Power Query

I recently set up a management accounts model using Power Query to combine multiple forecasts.

This is for a startup charity, where we need the ability to forecast at a high level over a five year period but to be able to update that quickly based on current decisions. We also need to do a detailed six monthly forecast for cash flow purposes. We also want to approve changes on a quarterly basis. Finally, we want to be able to assess the quality of our forecasting so that we can continue to improve it.

In other words, we need to have multiple versions of the budget / forecast while maintaining complete version control. I’ll use actuals from the financial system, but I’ll need to toggle my budget comparators to be able to show the board how we were doing against both the last budget and the latest forecast.

In this post I’ll set out the approach I took to handling multiple forecasts. I’ll also talk about some problems I came across and how I resolved them.

If you want to learn more about Power Query then there are loads of good blogs, You Tube videos etc, or you can commission me for training.

You may also find it helpful to read this piece first on creating a management accounts model.

Combining multiple files in Power Query

Power Query is brilliant for combining data from different sources, as long as the data sources are consistent. You can point a query at a folder containing multiple forecasts, and as long as the format of the data is consistent, it will amalgamate everything for you.

For example, if I had a budget template that looked like this…

…in a folder structure that looks like this…

… I can combine all the information from all of these files.

Just to be clear, I have a parent folder called “Budget example”, which contains subfolders for each forecast round. Each subfolder contains several spreadsheets with multiple forecasts.

Go on, then.

To make life easy for myself, I’ve formatted the budget information as an Excel table, with the name “BudgetTable”. It is possible to combine spreadsheets without doing this but it is much, much easier if you have an identically named table.

In Get Data, Select From File > From Folder, point the query at the folder and then press enter. This should then bring up a list of all the files in that folder, including any in subfolders. If you then press “combine and transform” you get a window that looks like this;

Select “BudgetTable” and voila! It will load a query that combines every instance of this table in the folder, WITHOUT YOU DOING A THING. Even better, in my version, it automatically brings through a column giving me the source name of the file:

So you can already see how helpful this is in combining multiple forecasts.

However, we’re not quite there. Can we bring in information from folder names (i.e. “DecForecast” and “MarForecast”) to allow us to distinguish between different versions of the budgets? Hell yeah!

How to cheat at Power Query

If you take a closer look at the queries that Power Query has created for you, you can often adapt them, rather than having to code it from scratch.

You can see that in order to combine files, Power Query has created the query that you’re in – “Budget Example”. Power Query has also created some “helper queries”. Effectively this tells Power query to look at one file, transform it in a particular way, then apply this transformation to everything else it can find.

This describes what Power Query has done, in what order.

If you’re new to Power Query, you need to know that you can go back through these steps, simply by clicking on them. And you can (carefully!) amend steps by pressing the settings wheel if it is present.

What I wanted to do was find a point in the query where the folder name was still there. It was still there in step “Renamed Columns1” but had disappeared as part of step “Removed Other Columns1”.

Going into the settings wheel for “Remove other columns” I could see exactly what needed to be done. I selected the column “Folder Path” (unticked in this picture).

The final query now looks like this.

And with a bit of tidying (using text extraction functions) you can get it to look something like this;

Sharepoint connection issue

Another issue I had was how to deal with data in Sharepoint. Because this was for a charity, they were using the free Business version of Microsoft365, not the Enterprise version. This means that you cannot connect directly to a Sharepoint folder.

However, there is a way round this. I set up a synchronized folder using OneDrive so that the budgets folder (complete with structure) was synchronized to my desktop. Then I pointed the query at the desktop version.

I don’t think this is ideal, as if anyone else wanted to run the query they would need to also do this and amend the navigation path in the query before it would work. But it’s a useful trick to know.

Future proofing – what if you want to add more periods?

When designing something like this, you always need to think about future proofing. Because I was looking at a moving five year forecast, I needed to make sure that if new periods were added into any of the forecast files it would still work.

Power Query is column based, and as such it gets upset very quickly if your columns change name, disappear, increase etc. It’s always possible to get round this with the right steps, but it helps to have thought of it before you get too far down the design route.

In this case, I found this excellent blog post which explains how you can ensure that your query will include new columns.

In conclusion

I’ve written this post to demonstrate that a little Power Query knowledge can take you a long way. I had never learned how to add new columns into a query before, but I assumed it would be possible, g**gled it and applied the results to what I was trying to do.