People sometimes ask: “Can you give us an example of Power Query uses”?
(In fact, they often ask, “Can you give us an example of Power Query use cases” but I hate that phrase with a passion and refuse to use case it.)
So here are some very recent examples of Power query uses from a part-time FD role that I’m doing at the moment. It’s for a small but not tiny charity with lots of systems that don’t talk to each other.
Gift Aid manual uploads
Within a few days of starting I’d written a couple of Power Query templates to speed up some processing. The charity captures Gift Aid information via the website but (for reasons) currently has to use the manual upload method to actually claim from HMRC.
With Power Query it’s really easy to take the website report and turn it into the exact manual upload template required by HMRC. (If you’ve ever done this, you’ll know how finickety this is!).
It took me about 20 minutes to write a template that will now work in seconds each time.
Quicker journal templates
The charity finance staff post journals based on what’s gone through unconnected till systems. This has meant copying and pasting data from a report into a spreadsheet, which took time and was fairly error prone.
I used Power Query to create a template that turns the till report into the correct format for the journal template that’s needed for their accounting system.
Management reporting
One of my most common Power Query uses is management reporting. I’ve posted before about my approach to creating a management accounts template in Power Query.
I’ve now got a management accounts model that allows me to refresh actuals in a matter of seconds. It also allows me to quickly rephase budgets for forecast updates – really important when we’re trying to accurately predict cashflow.
What’s next
There are lots of other areas where I can use Power Query to reduce manual processing time. For example, in adding together timesheets or posting credit card allocation journals. I’ll also be implementing an Irrecoverable VAT calculation process.
In the longer term we’ll want to introduce systems that talk to each other properly. But in the short term, Power Query allows us to create capacity within the finance team to do the longer term system changes.
Power Query uses – not just reporting, and not just big data
Only one of the example uses above relates to reporting. There’s a misconception that Power Query is only useful in the context of Power BI or other reporting scenarios. But I use it just as much for “financial operations” tasks as reporting tasks.
My half day Introduction to Power Query course is based entirely on useful examples from my professional career.
Contact me if you’re interested in organising this for you or your team.
If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.