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:
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
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
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.
Thanks Andrew, and sorry not to have answered earlier (email comment notification only just received). I completely agree regarding renaming each step, doing so makes a considerable difference to how easy it is to follow what each step does and it is a really good habit to get into.
Thanks to everyone who has voted. Pleased to see that the article seems to have at least polarised opinions.
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?
Hi Malcolm, you make some excellent points (though the client quote referred to Power Query rather than Power Pivot). However, I'm not sure I agree with all those arguments. Accountants also did their jobs before calculators and computers. If an accountant took 10 times as long as necessary to produce a set of accounts because they didn't want to use a computer, would that still be doing their job properly? Also, I entirely agree with your statement " if your courses don't require you to use PP, then that doesn't make you a bad accountant." but what if your courses do require you to use PQ but you don't know anything about it? Does that make you a 'bad accountant'?
Accountants did their jobs properly before Power Pivot came along: they can still do their job properly without using it now. Whether they can do so things as fast is another matter. Power Pivot is great for analysis and reporting and I've reduce the complexity and size of many of my spreadsheets using it. But it has its limitations. Its error trapping, for example, is minimalistic. Users are forced back into hunting through the underlying calculations: there is no way of integrating an error trapping regime which validates data and allows users to correct it through dialog boxes in the way you can do with VBA. Tables which require cumulatives, or FIFO calculations for example, are far trickier to implement in PP than with old-fashion formulae. And it doesn't do instantaneous calculations (unless you write an auto refresh macro and then it's still slower than old-fashioned basic). So at present, it is horses for courses and, if your courses don't require you to use PP, then that doesn't make you a bad accountant. That said, I definitely wouldn't want to go back to the pre-PP days for some of my work.