Excel Power Query can save hours of time for people who have to take data from multiple sources and reorder it for reports and analysis.
Liam Bastick is on a mission to change the world “one accountant at a time”, and he managed to recruit one new follower at a workshop event in Brisbane in 2018.
During a break in the session, a financial controller approached him and explained her dilemma.
Just over two years ago, her assistant left the company and there wasn’t sufficient budget to hire a replacement, so the financial controller took over one of the most tedious tasks: reordering data and putting it all into Excel to create a report for the board.
Typically this took about five-and-a-half hours a week, and because of her existing workload, she did it on the weekend at home.
Saturday afternoons were dedicated to this work, during which time her husband took their two young children out so she could focus on the task.
“Her IT department was telling her that she needed a new computer, and she came to me in the break and asked if there was anything I could suggest that could help,” says Bastick, a director of data analytics consultancy SumProduct, who is also an accredited Microsoft Most Valued Professional (MVP) for his Excel knowledge.
“I showed her how by using Power Query she could generate that report in eight seconds.”
Cutting time with Power Query
The immediate result, says Bastick, was a look of delight and despair on her face.
She was delighted to have a quick solution, but despaired at all the Saturday afternoons she had given up for work that could have been done with a few clicks of a mouse.
For Bastick, it was another victory in what he says is his “crusade” to spread the word about Excel as an – almost – free and ubiquitous “self-service” business intelligence tool that can make the working lives of people dealing with data much easier.
People don’t need to be data experts, he says, to join the “point and click” brigade, which harnesses the power of Excel to simplify what are otherwise laborious and “mind-numbing” tasks.
“If people are telling me that they don’t have the time to go and learn this stuff, then I tell them they are precisely the ones who need to learn,” he says.
“I see people wasting their lives manipulating data, but if you go on a three-day course, at the end of two months you will have made up that time, and you will never look back.”
For accounting, he says it is part of a major generational change. Where accountants today might spend 85 per cent of their time preparing data and 15 per cent analysing it, “going forward it’s going to be the other way around”.
“I think this is really an exciting time to be an accountant because finally you’ll get to do what you are really paid for, and that is analysis,” says Bastick.
Greater analytical power with Power Query
Among Bastick’s favourite Excel functions to support his “crusade” is Power Query, which is now part of what is called Get & Transform in Excel.
To find it, users need to go to the “Data” tab and the functions that appear form part of the functionality of Power Query.
This enables users to bring in data from multiple sources, including from the internet, and then it can be worked with in a window called the Query Editor.
Where Excel is limited by the number of rows – the maximum is 1,048,576 – Power Query does not use this interface, so the only limitation is the memory on the computer, and for this reason Bastick favours 64-bit computers.
If over one million rows sounds like more than anyone would need, Bastick cites an example of his work with a leading utility company where it input over 900 million records for analysis.
While it doesn’t “make the data look pretty and presentable”, Power Query is more like a workshop in which the data is aggregated and worked on for later presentation in charts and tables.
The word is spreading, and the financial controller in Brisbane is not the only convert to Bastick’s “point and click” brigade.
He cites the example of a CFO whose company has 30 business units.
Salaries are paid monthly, and the CFO has to approve the payroll, which involves receiving information from all the business units and consolidating it into one spreadsheet.
“This takes him about a day and a half a month,” says Bastick.
“We showed him how he could click on attachments and copy and paste to the same folder and do it in a few minutes.”
The CFO, says Bastick, was amazed, but didn’t trust the Power Query method, so the first time he used it he also used his old method, just to check it was right.
“Once he trusted it, he left the old method behind and saved himself that day and a half each month,” says Bastick.
Liam Bastick and colleagues from SumProduct will be presenting Excel masterclasses at CPA Congress in Queensland, New South Wales and Tasmania during October.
This article was originally published in IN THE BLACK