Power Query and accountancy

This statement might be going a bit far, even for as fervent an advocate of Power Query as I am. However, it's what one of my clients said after we had used Power Query to revolutionise their month-end management accounts process.

Of course, not all accountants need to use spreadsheets at all, some use spreadsheets in a way that doesn't involve the use of any complex formulae or functions and some will already be using alternative solutions that work in a similar way to Power Query.

Going back to the client, their previous system had involved several interlinked workbooks with data being manually copied and pasted from an accounting system. Thousands of lookup and conditional sum formulae were used to analyse and summarise the data with frequent manual interventions required to cope with changes in the content and volume of the underlying data. As the system had been developed over many years, with several different members of staff involved at different times, nobody was entirely sure they knew exactly how the system worked.

Using Power Query to extract and manipulate the data allowed us to make the process almost completely automatic and eliminated nearly all of the cell-based formulae. In fact, in about the same time that it used to take to produce each month's set of accounts, we were able to set up an entire system based on the use of Power Query that requires little more than a single click of the Data, Refresh button each month.

One of the concerns I had when I first started using Power Query as an alternative to legacy Excel, related to the ease with which users would be able to manage and troubleshoot the resulting system for themselves. One of the potential advantages of cell-based formulae is their visibility. In addition, users will generally be familiar with the way that the formulae and functions work and should be able to identify and correct most issues. In contrast, Power Query sits invisibly in the background and requires different ways of thinking and different approaches to troubleshooting. In practice, this hasn’t turned out to be a significant problem. Not only have the users of the systems, most of whom are accountants with reasonable Excel skills rather than being IT experts, been able to sort out most issues that have occurred for themselves, where an issue hasn’t been so straightforward, it has proved easy to identify and resolve it remotely.

Although troubleshooting in Power Query is very different from sorting out Excel formula issues, the procedural nature of queries and the ability to go through them a step at a time makes it easy enough to pinpoint exactly where any problem occurs. For example, displaying the Queries pane shows all the queries present in the workbook and also highlights any that fail to update or include errors:

Hovering over a query that generates an error displays a detailed explanation of the problem and, when the query is opened for editing, clicking on any step after the step that generates the error will display a Go To Error button that takes you straight to the problem step. Where an issue doesn't stop the query working, but does result in one or more of the returned rows including an error, the Queries pane will display the number of errors as a live link that can be clicked to create a table listing all the error rows for investigation.

Over the past two or three years, I have worked with clients on using Power Query to solve a wide range of accounting problems, from obvious uses of Power Query such as extracting and manipulating data from external systems, to using Power Query as a more automatic replacement for legacy Excel approaches within a single workbook. In most cases, we have been able to replace complex solutions, that relied on hundreds or thousands of individual cell formulae, with far simpler, data-based, processes that were much quicker to set up in the first place and which were able to fully automate processes that had previously required extensive manual intervention.

I'm pretty sure that the vast majority of businesses have existing systems in place that could be revolutionised using Power Query to create more robust and automatic solutions. Accountants, both in business and practice, would seem ideally positioned to help achieve this productivity revolution.

If you would like to investigate the potential of Power Query further, on July 4th I will be presenting a webinar entitled: "Experimental Excel – look, no formulae" which, in the unlikely event that all goes smoothly, will show how to use Power Query to completely automate the consolidation and summarisation of multiple trial balances to create a set of formatted management accounts.

In addition, there are several articles in Tech News and in the Excel Community looking at many different ways to make use of Power Query:

Tech News (open)

An exciting time for Excel users – the added dimensions of deferred income

Excel and rechargeable vacuum cleaners

Management accounts and the Power Query revolution – how you voted

Excel – dealing with duplicates

Excel Community (restricted to Tech Faculty and Excel Community members)

Web scraping – the versatility of Power Query

Super Power Query – 1: dynamic transpose

Super Power Query 2 – text to values, American format dates and more

Excel Power Query / Get and Transform for reconciliation purposes

Power Query and financial modelling – the 2 year challenge

Experimental Excel series:

Part 1 – introducing Power Query

Part 2 – consolidating data from multiple files

Part 3 – merging tables instead of using a lookup function

Part 4 – formatting a Power Query table

Part 5 - Experimental Excel – Mission Accomplished or Mission Impossible?

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

No Data
  • This is a helpful reminder that automation is important, but simplicity of process and documentation is also necessary. The things I love about Power Query is that is allows the logic of formulae to be defined, but in a way that precludes dangerous single cell exceptions.  It also allows step-by-step logic to be defined, without the need for multiple columns/cell that a traditional formuia-based best practice would recommend.  Finally, it forces the user to consider how to improve the source data quality (fix problems at source) rather than writing formula exceptions to cope with poor data from poor upstream processes.

    One thing I like to do, which  your article does not outline, is to rename each step in the query build with a 'meaningful' name, to provide documentation of the logic and therefore a more intuitive way for subsequent users to use / debug. Thoughts on this Simon?