Why isn’t Power Query better known amongst finance teams?
I organised a short Power Query demo a couple of months ago for a group of senior finance people in small not-for-profit organisations. This was a group of people who I’m in a professional network with, who are all Finance Directors or managers. Someone asked about tools for improving reporting, I mentioned Power Query and as it became clear most people hadn’t even heard about it, I offered to do a 20 minute demo. As is always the case, the attendees came away keen to discover how they could learn more.
This is something that happens time and time again, and I constantly wonder how this fantastic tool box is still so unknown to the people who would benefit the most from it? By which I mean small teams without sophisticated finance systems who do a lot of manual copying and pasting into and from Excel to transact and report.
I have two main explanations for this.
Fear that Power Query is too complicated / only relevant to large organisations
Articles and guidance usually presents Power Query 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. So it’s really easy to get the idea that it’s only useful for Finance, Planning and Analysis (FP&A) or Management Accounting roles, in the sorts of organisations that are big enough to have a dedicated FP&A function.
I mean, look at Microsoft’s “what is power query” page. The first phrase that leaps out at me is “Microsoft Azure Data Lake Storage” – not exactly a common phrase amongst finance teams or the average Excel user.
Power Query is not obvious within Excel unless you’re looking for it.
There isn’t actually something called “Power Query” in modern Excel. It was first called Power Query but has now become Get and Transform Data. Until you actually go into it, where it is still known as the Power Query editor.
Further reasons – and my responses
I asked the community on LinkedIn about this and got many interesting responses. As well as variants on the two points above, people also said;
- some users had expressed concerns about auditability;
- a misconception that it’s another coding language;
In relation to the audit point, please let this ex-auditor tell you that I’d much rather audit a Power Query than a random spreadsheet. Power Query sets out the steps that a user has done to transform the data, and you can literally step back through the query to see how the data has changed at each point.
As for the use of code, I wonder if this is because the early adopters were (by definition) enthusiastic about all sorts of technology and therefore were comfortable diving into the code behind Power Query and writing cool blog posts about what you can do with it. This is great – I’ve learned loads from these sources – but it does mean that search engine results for “power query” is dominated by examples of this, rather than the simple transformations you can do without code.