Back to basics: Why would you bother investing the time to learn Power BI when you already have Excel?
Interest in Power BI is growing quickly and I suspect that nearly all new users of Power BI have already been using Excel for some time.
So if you already have and are familiar with the most flexible and ubiquitous data analysis tool on the planet (Excel), why would you invest the extra time and energy to learn to use Power BI?
Why reach for a product that is making claims around artificial intelligence, when perhaps you still need to get to grips with your basic management reporting? The answer is in the question. It is actually to help get those basics right.
If you are working with spreadsheets that are too big to see on a single screen, or need to combine information from more than one spreadsheet then you may really benefit from using Power BI.
To examine why, we need to look at the core essence of Excel and Power BI and what you do with numbers when you produce your management reports.
Every great product has its core essence, then things are added on top
The spreadsheet (aka these days as “Excel”)
Content seriesView full content series
What makes a spreadsheet so great is its interactivity, transparency, flexibility and intuitiveness. You can see what is going on, instantly. The concept is incredibly simple. At its core are cells.
Each cell contains a formula and a value that is the result of the formula. The formula can use as its inputs the values (outputs) of any number of other cells. Which in turn can reference the values of other cells, and so on. When you look at a cell, you see its value. When you click in a cell, in the formula bar you can see the calculation that is driving its value.
When you change the formula or value in a cell, then all of the affected cells are recalculated and their results displayed; immediately. This is so powerful and flexible. It is no wonder that the spreadsheet (now in the form of Excel) has turned out to be one of the most successful software products of all time.
Problems with spreadsheets start to creep in when they become too big to see the whole thing on a single screen and especially when you want to combine data from more than one sheet. The very idea of the spreadsheet is to be able to see what is going on. Take a look at the image below. It is a screenshot of an Excel sheet of transaction lines derived from Sage 50 Accounts.
This spreadsheet has a little over two-years’ data (enough to do this year vs last year comparisons). It comprises 57,491 rows and 19 columns. That gives a total of 1,092,329 cells. Without zooming out, I can only see 560 cells at a time on my laptop screen, or just 0.05% of all of the data. This is no longer intuitive, transparent nor interactive.
To put this in context, the image above is the Transport for London Tube map, but it has a blue card in front of it with a tiny hole that means that I can only see 0.05% of the map at any one time. I am currently looking at Oxford Circus station, believe it or not.
I can move the card around to my heart’s content, but my view is always restricted to 0.05% of the map. Imagine the chaos, confusion, mistakes and wasted time if this were the only Tube map available for the 30 million visitors that visit London every year!
I think that you see the point. If you cannot see all of your Excel cells that you are using, on the screen, why would you use them as the primary way to explore and interact with your data?
As happens with immensely successful products, Excel has grown legs; features and plugins have been added over time to help fix, or workaround problems like this. These include the basic Pivot Table and Pivot Chart and the technically very advanced PowerQuery, PowerPivot and PowerView extensions to Excel. This approach may solve problems for hardcore Excel users working with large datasets, but to me it kind of has a “I wouldn’t start from here” feel about it.
The core essence of Power BI is about columns and not cells
The first version of Power BI Desktop was essentially the Excel plugins “PowerQuery”, “PowerPivot” and “PowerView” recast under a different user interface. In other words, providing a new starting point that doesn’t involve trying to present over a million cells on a screen that can only display 560.
Whereas the basic concept of an Excel spreadsheet is the cell and its relationship to other cells, that of Power BI is the column and its relationship to other columns. This can take a little getting used to, but is arguably more natural.
The Power BI thought process is closer to plain English than the Excel thought process. One column might be “NetAmount” and another might be “Cost”. In plain English you would say that “Margin = NetAmount minus Cost”. You don’t need to know how many rows are in a column and any individual cell references. You just need to be able to specify the columns and the calculation.
Power BI makes the opening assumption that you cannot see all of your data in one big array of cells. Instead, it presents by a screen onto which you can place visualizations to present views into and summarizations of your data.
If we go back to our Transport for London Tube Map, fortunately it isn’t covered by a big blue card with a tiny hole in it. Or is it? The way that many of us look at the world now is through our screens on our smartphones and certainly trying to look at a full Tube map on a smartphone is not a great experience.
The solution instead, is a totally different experience in the form of an app called “CityMapper”. This provides a simple screen to help you filter and select your start and end points from its underlying map database and another filter to select when you want to travel. It then adds up the travel time for each element of your journey for alternate route options that are summarized and presented on the screen.
In other words, instead of presenting a map of the TFL network and timetables, it simply provides a simple way to specify your query (filters for time, start location and end location) and display the results on a small screen.
You can select an option and drill down to the details, as far as knowing which end of a train to travel in, which station exit to use and your estimated walking time.
Power BI kind of works in the same way. You decide what it is that you would like to see from your data and configure a set of visualizations to show this and use these visualizations to drill down to more detail. You will need to create your underlying data model, just like you would need to in Excel.
This is like working with Excel formulae, but instead of defining formulae and relationships at a cell level you are doing it at a column (eg NetAmount) and entity (eg Customer) level. This is much closer to how we think and a lot more robust.
In my opinion, the core essence of Power BI is its ability to gather data, then filter, summarize and present it in a way that can easily be understood. Surrounding this is a host of features that help you to manipulate your data as well as publish and share your results.
How we work with numbers to produce a management report
The first thing to notice in the numbers section of any management report is how simple the maths is. It is nearly always simple addition; for example to summarize a set of values that are filtered by time period, nominal code, department, or some other dimension. We are summarizing columns of numbers (e.g. transaction amounts), filtered by entity (e.g. product or time period).
There is a clear presentation of the summaries through some useful graphs and charts.
Behind the scenes, you need to gather, check and prepare the data ready for this summarization.
This sounds a bit like Power BI doesn’t it?
Preparing management reports involves gathering data, preparing, filtering, summarizing and presenting it. Unlike Excel, this is exactly what Power BI is designed to do at its very core. So, if like many other accountants you are working with large spreadsheets or multiple spreadsheets to produce you management reports, maybe it is time to take a look.
About Hugh Johnson
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs, sales reports & forecasts, liquidity & cash flow reports, stock reports, and accounts receivable analysis from Sage, Xero, Exchequer, Microsoft Business Central, Iris, and CCH.
I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year).
My personal passions are high-performance Fireball dinghy racing and food.