"I can see that Power Query could save my team lots of time, but how would the auditors be able to cope with it?"
Having delivered my first two lectures focussed on the use of the Excel Power Query tools earlier this week, I was particularly interested in the delegates' reaction to what was, for many, a radically different way of approaching a range of practical Excel challenges. The above question about auditors and Power Query raises two different issues. Firstly, how does auditing a complex spreadsheet model that uses Power Query compare to the legacy Excel, cell-based, equivalent? Secondly, how will auditors react to a switch from cell-based solutions to Power Query based solutions?
On the first question, I argued that Power Query is vastly easier to audit than legacy Excel. Where a cell-based solution would generally require calculations and operations to be performed on every row in the data individually, Power Query creates a process whereby each operation or calculation step is applied to entire columns of data. So, instead of thousands of individual cell formulae, any one of which could contain an error, an equivalent Power Query solution might just contain a dozen or so steps, each of which is consistently applied to every row of data. In addition, the Power Query editor allows you to step through every operation allowing the result to be separately assessed. Compare this with the difficulty of isolating the effect of each component of each Excel formula in thousands of different cells. Also, Power Query also lends itself to the simple creation of checks, pre-checks and cross-checks. For example, the Merge Queries feature makes it simple to generate a list of all the items that exist in one table of data but for which there is no match in another.
On the other hand, formulae in cells have the benefit of being visible on the face of the worksheet, making it possible to spot errors and unusual results and to use techniques such as Conditional Formatting to highlight such cells. In contrast, Power Query performs its operations outside of the workbook itself, applying a series of operations in the background and just delivering an end result (subject to the inclusion of specific pre-checks and checks as described above). There is a comparison to be made here with PivotTables which are much-used and have been around a lot longer than Power Query. Again, the calculations and operations are largely carried out 'invisibly', with the PivotTable itself being the end result. Where the use of Power Pivot can give similar levels of visibility to Power Query, a simple PivotTable can hide the background data in the mysterious PivotCache.
On the question of how auditors would deal with Power Query, I'd really like some feedback. The Power Query tools have now been around for nearly 10 years so some auditors are likely to have come across client spreadsheets that use Power Query and some accountants will have had systems that include Power Query assessed as part of an audit process. It would be very interesting to hear Power Query related experiences from either side of the audit process.
Power Query itself could also have a significant role to play more generally in the audit process. As described above, it can be used to generate checks and to create tables of exceptions. It could also be used as a way of generating parallel calculations to check the results of existing spreadsheets or other systems. Is anyone already making use of Power Query in this way?
On the more general question of whether all auditors need to understand Power Query, it could certainly be argued that auditors are always working with different IT systems, so Power Query in Excel makes little difference. However, because Excel is so ubiquitous, any systemic change in the way that it is used might create particular issues. Where many IT systems that auditors are required to cope with will be off-the-shelf packages, developed and supported by experts, spreadsheets are frequently used for bespoke, custom solutions. Also, they are often the province of end-users whose main expertise might be in other areas. Accordingly, Power Query could present some particular challenges., particularly to those that audit smaller organisations that make extensive use of spreadsheet solutions but might not have a dedicated IT team.
Looking beyond audit to general business advice, I am convinced that the use of Power Query and similar tools can make a vast difference to the efficiency and reliability of a whole range of business systems and processes. Is it possible to provide the best advice to clients if you are not comfortable working with such an important and accessible feature within an application that almost all clients will be using in some way or another?
What do you think?
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.