Power Query has been out for over a decade and yet the majority of accountants I meet still seem unaware of what it can do for them.
A possible issue is that it is usually presented in the context of Power BI, and analysing Big Data. And the type of people who like to talk about that also like to talk about other scary things such as VBA and SQL. There aren’t many training resources or articles focussing on Power Query for accountants more generally.
Certainly my epiphany came when I actually got to see Power Query in action with a specific and practical problem I was having with management reporting. But now I’ve learned it, I use it practically every day. Sometimes to do a large project, such as creating a year end accounts model. But more often than not, it’s to do a mundane repetitive task. For example, turning a list of planned payments from Xero into a bank file. Or to generate a journal to post irrecoverable VAT to specific budget and funding lines.
I recently wrote a series of articles for AccountingWeb with this in mind. I put together a step-by-step guide to how you could use various Power Query functions to add data to a trial balance.
Part 1 covered basic navigation, data types, and adding simple calculation columns. Part 2 looked at conditional columns and merging queries to add categorisation columns. Then, in Part 3 I developed these ideas further and demonstrated how to append budget data.
Along the way I showcased various of my favourite features – for example, “split by delimiter”, “unpivot”, and “fill down”. These are all functions that make day-to-day use of Excel easier. And there is no need to learn a complicated programming language – you can use these functions without going anywhere near any code.
Read more here about how I can help you learn Power Query.
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.