Director Accounting Insights Ltd.
Share this content
Tags:

Power BI: Why would you, when you already have Excel?

4th Apr 2019
Director Accounting Insights Ltd.
Share this content
Data

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.

Power BI

Every great product has its core essence, then things are added on top

The spreadsheet (aka these days as “Excel”)

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.

Spreadsheet

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.

map

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.

Citymapper

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?

Summary

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.

Useful links

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By Excelcrafter
05th Apr 2019 02:42

A good overview, Hugh. But I think you skip over the fact that the data engine in Power BI is the same as Power Pivot in Excel. So someone wanting to learn Power BI could get their feet wet with Excel and Power Pivot. Having built the data model and become comfortable with it, they can upload it to Power BI to take advantage of its sophisticated visualizations user-friendly interface.

Thanks (1)
Replying to Excelcrafter:
avatar
By Practicaledd
12th Apr 2019 18:48

Agreed, that’s exactly what I’ve been doing. It’s nice being able to do things both ways to learn when to use new methods (and to get comfortable with them).

Another side effect for me has been the way I think of the underlying data. I’ve found myself working with clients to make it cleaner at source rather than doing the same old cleaning activities all the time. That’s led to some interesting insights into their process etc.

Thanks (0)
Hugh Johnson
By Hugh Johnson
11th Apr 2019 09:42

Thanks. I agree with you. To an extent. My understanding is that the very first edition of Power BI Desktop was in effect the Excel plugins PowerQuery, PowerPivot and PowerView bundled together under a new user interface.

You can absolutely get your feet wet first with Excel and PowerPivot then migrate to Power BI. This is very much a personal choice. The DAX functions that you would learn to use in PowerPivot are the same as those you would use in Power BI. The underlying concept is the same.

My personal view, however, is that if you have never used PowerPivot before, then it is easier to dive straight into Power BI and skip PowerPivot. I think that the UI is better laid out and optimised for the purpose. This is just a personal view though.

It does, of course, mean that if you are already familiar with PowerPivot, then a transition to Power BI would be very straight forward.

Thanks (1)