Millie Sadler, Finance Business Partner at NAO, describes Excel as a ‘comfort blanket’ for accountants. 

Excel offers a wealth of core functions and tools which make financial reporting and data analysis relatively easy: conditional formatting and PivotTables, to name but a few. Power Query in particular. Essentially ‘get and transform data’ on the Data ribbon, it’s a data transformation tool which allows users to pull data from several sources, works across platforms including Excel and really brings added value. 

But Excel also has its limitations. It can be clunky, formulas can be complex and it requires a lot of time-consuming manual input. Power Query could be the gateway into doing more with your data using Power BI.

Time to bring in Power BI

Microsoft’s Power BI, an inherently powerful data visualisation platform, can handle huge data sets and with add-on products such as OneLake, its capacity can be expanded further.

“One of the main benefits of Power BI over Excel is its ability to autonomously refresh financial reports or dashboards without the user having to lift a finger,” says Sadler. “With Power Query in Excel, the user needs to go into the document itself and click the refresh button each time they want to update the record. But with Power BI, once a report is built, you can schedule updates, so it always provides a near-live position.”

In practical terms, Power BI is a great tool for monthly management accounts and income forecasting, which is exactly what NAO and Sadler are currently experimenting with.

The finance department at NAO still uses Excel for monthly management accounts but with Sadler’s help, they’re wanting to migrate to Power BI.   

“Using Power Query with Excel is more time-consuming,” says Sadler. “You have to collect and download lots of reports from our accounting software, migrate them into Excel, ensure formatting is correct and once you’ve clicked ‘refresh’, you hope it all works. And if something goes wrong, you could spend hours unpicking everything.”

It can save time

Currently, the process takes Sadler and her team around two and a half days a month, but once they’ve achieved proficiency using Power Query within Power BI, it could reduce the time to just hours.

So, what do accountants keen to start implementing Power BI into their working practices need to do?

“Carve time out in your schedule to really familiarise yourself with Power BI,” Sadler recommends. “A lot of the functions in Power BI are fairly similar to Excel so it may not be as hard as you think. Block off set time each week to experiment with it. There will be a learning curve, but the time investment is worth it.”

There’s also the ICAEW-certified Analytics in Power BI course which launched this month and which Sadler herself contributed towards.

Change in action

Here’s an example to compare approaches in Excel versus Power BI.

Say you want to calculate the total sales for the Europe region. In Excel, you might use a combination of filters and subtotals, or maybe create a PivotTable. If you’re feeling confident with Excel tables and formulas, you might use something like:

=SUMIF([Sales Region], “Europe”, [Sales Amount])

Or you might need to reference a specific range of cells.

This is fraught with challenges – the data has to be in Excel to start with, it may be prone to rows or columns shifting, and if the user wants to perform the calculation for a different region, they have to either create a new formula or edit the existing one. This impacts the audit trail and increases the risk of ‘spreadsheet errors’ (aka human errors that happen to be caused by working in a poorly managed spreadsheet).

In Power BI, the basic steps are clear:

1. Connect to the data source – this could be a direct connection to the accounting package.
2. Check data quality and perform basic transformations using Power Query before loading the data into Power BI.
3. Add a new measure (a more powerful version of the Excel formula, using a special language called DAX). Instead of SUMIF, your measure might look more like this:
   Total Sales for Europe =CALCULATE(SUM([Sales Amount]),[Sales Region] = "Europe")
   In essence, it’s achieving the same goal: calculate the sum of the ‘Sales Amount’, where the ‘Sales Region’ is ‘Europe’.
4. You can now add visuals in Power BI to showcase this value, perhaps over time, or by salesperson or product.

While it initially seems more complicated in Power BI, the steps are clearer, more auditable, and allow for more dynamic, flexible analysis. You could even do without the measure entirely, creating visuals that aggregate the sales amount from the source data, and allow the user to perform their own filters dynamically.

The move from Excel to Power BI is not without its challenges. But taking Power Query as the stepping stone, the reward is definitely worth the effort.