This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.
In our last three meetings in this virtual space, we spent quality time getting to know Excel’s Power Query tool. We saw how Power Query transforms data and how accountants can use it to automate bank reconciliations. I also had the privilege of introducing you to my favourite Excel feature, which resides in Power Query.
This month we turn our attention to another Microsoft application that includes Power Query functionality.
Microsoft Power BI combines Power Query and PowerPivot technology with a user-friendly charting and reporting interface in an enterprise-friendly dashboard reporting/sharing framework. Accountants can use Power BI to extract, transform, and analyse data from a multitude of sources in real time.
This is no trivial matter. Accountants are uniquely qualified to mine data for business insights, but they usually spend much more time preparing the data than undertaking the value-added analysis. A business intelligence tool such as Microsoft Power BI may help reverse that trend. That promises to be a big deal as technological advances such as big data and 5G open the gates to a flood of real-time business information.
Power BI is far from the only business intelligence software available, but it’s not as expensive as many of the other BI tools (Tableau, SAS, SAP) and it’s easily accessible as part of the Microsoft ecosystem.
Most users can choose from three Power BI options:
Let’s be honest, the first analytical tool most accountants reach for on a daily basis is good old Excel. Many accountants still use Excel to produce financial insights for managers and C-level executives. However, when it comes to creating reports and sharing them throughout an organisation, Power BI can take advantage of the hard work performed in Excel spreadsheets by extracting data to create reports.
You can find data in various forms and from many sources: enterprise data, cloud-based platforms (eg, Xero, QuickBooks Online, and Facebook), and Excel spreadsheets, to name but a few. Power BI lets you bring it all together in one place to visualise. With this direct link in place, Power BI really simplifies your period-end procedures, as it can automate the extraction from different systems and combine them together as opposed to manually doing it yourself. You simply press the Get Data button on the Ribbon:
While Power BI is much more flexible than Excel when it comes to creating and sharing data visualisations, its underlying query tools are mainly built into Excel, too. In Excel 2010 and Excel 2013, you can download the Power Query add-in from the Microsoft website; in Excel 2016/2019 and Office 365, the Get & Transform feature is built into the Data tab in the Ribbon:
Presently, the technology in Power BI is more advanced: Any new features are Generally Available in Power BI before they are introduced to Excel. Excel is more than 30 years old; Power BI, being newer and designed for these sorts of tasks, tends to be faster and more efficient at getting and transforming your datasets.
Your initial conclusion might be to use Power BI rather than Excel. It’s not as simple as that, though:
Excel is designed for ad hoc analysis. The great thing about Excel is that it’s powerful and flexible enough to do what you want to do and relatively easy to use. This is why so many of us use it: It’s simple to put in place manual processes and tweaks in order to get our reports to look and calculate exactly the way we want.
The problem is that we often rely on this flexibility and end up locking ourselves into undertaking tasks manually (eg, copying and pasting, copying formulas down columns and across rows, updating dates, deleting items, and re-sorting).
Power BI is designed for business-as-usual reporting — quarterly, monthly, daily, real-time, etc. It’s not so easy to perform ad hoc calculations outside of the “standard” reporting tools that you have — you can do it, but it may take longer than it would in Excel. However, where Power BI shines is its ability to automate the process of data cleansing and transformation, and to set up consistently applied calculations that span the entire dataset, without the need to manually adjust and tweak the formulas.
Further, Power BI’s online service allows you to share the results of your reports. You can simply provide access to the live dataset to a colleague, rather than email a file and run the risk of having version control issues.
Therefore, perhaps Power BI may be more suitable for regular reporting processes. Once the reports and dashboards have been set up, it’s a case of set-and-forget — the reports will continue to be updated without further input from users.
Such a set-up allows you to reduce the amount of work that is performed on a regular basis, and report consumers can log in and access their reports on demand, without the need for manual intervention. This could even be set up as part of the budgeting process, where each business unit saves its files in a common location to be picked up by a Power BI consolidation report.
However, if you require the data back in Excel afterwards, or calculations may not be consistent each reporting period, it’s generally faster to make the change in Excel. It also allows you to customise your calculations so they apply only to certain rows or columns. Finally, it gives you more flexibility to highlight and flag which cells and tables might be works in progress, rather than finalised. For example, transaction or financial modelling work, due to the ever-changing nature of a deal, is arguably better to perform in Excel.
In summary, which to use? It’s the common sage piece of accounting advice: It depends.
When you build reports, you need to be able to share. Historically, this would be undertaken by printing to PDF (say) and circulating the report. Power BI lets you share the same report, but now with live data and interactivity to drill down into the things that matter most.
Dashboards may be shared and viewed through publishing to the Power BI Service:
An Excel file may be published to a custom workspace in Power BI Service, too:
Power BI files can also be shared in Microsoft Teams:
Choose Power BI when adding a tab in Microsoft Teams…
…and add a file to share.
Since every application is connected in the Microsoft ecosystem, sharing is extremely simple.
Having emphasised how easy it is, it’s important to note that you may restrict access to data at the row level (record) by employing row-level security, where the visibility of specific data is limited to specific users. This can be set up in both Power BI Desktop:
and in Power BI Service:
This security feature allows you to set up rules using Data Analysis eXpressions (DAX) filters that restrict users from seeing certain information (eg, different divisional data, products and services, or salaries). For example, for an Australian manager, we may set up a restriction that he or she may only view Australian data:
In this instance, anything that is related back to the Geography table with a country name other than “Australia” will be excluded from the reports and dashboards that they are able to view. This way, you do not need to create myriad data tables, dashboards, and reports. Simple!
Power BI’s capabilities and use cases are far too vast and varied to cover in one article. You can’t do it in two either, but that won’t stop us from taking a look next month at the programming languages that underpin Power BI and how artificial intelligence really revs up Microsoft’s business intelligence software.
— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at [email protected]. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at [email protected].
FM is published by AICPA & CIMA, together as the Association of International Certified Professional Accountants, to power opportunity, trust and prosperity for people, businesses and economies worldwide.
Copyright ©2023 Association of International Certified Professional Accountants
Leave a Reply