Dynamics Matters Podcast: Ep 55 - Microsoft Excel vs Microsoft Power BI

Welcome to episode 55 of the HSO Dynamics matters podcast.

Your regular sonic dive into the world of Microsoft technology related matters and much more besides.

I’m your host Michael Lonnon, and in today’s episode I grabbed HSOs resident Power BI expert, Laura Graham-Brown for a chinwag.

I wanted to find out from Laura what the difference is between Excel and Power BI, and when would you use one over the other.

For anyone spending laborious hours maintaining excel based report, this episode is for you.

So, grab a brew, sit back, relax, and enjoy the show.

Michael Lonnon

Power BI or Excel?

Laura Graham-Brown

Power BI – Mostly.

Michael Lonnon

When might you use Microsoft Excel versus Power BI, or when might you use Power BI versus Excel? When do they to interchange?

Laura Graham-Brown

Excel is pretty good when you just want to do a check down, do some conditional formatting and do some checking through and you’ve got a list of 100 people, you just want to check them off or that they’ve come in, you can do editing in Excel, which Power BI doesn’t do. Also, Power BI has some awesome visuals, but it does not have a pivot table. Excel pivot tables are unique to Excel.

Michael Lonnon

Do you think that’s missing in Power BI?

Laura Graham-Brown

I don’t know if they’re missing it. There are ways of doing the kinds of things you can do in a pivot table. In Excel, you can do pivot tables, and then do calculations next to it. People who have to play with data and do calculations, they’re not just looking for results, they’re looking to explore the data that sometimes is really useful.

Michael Lonnon

When might you use Power BI versus Excel?

Laura Graham-Brown

The advantage of Power BI is that there’s multi factors. One is, it’s easier to tell a story in Power BI, to walk through it as a whole so using bookmarks and things and to tell a story. In the last few days, Microsoft build just announced some really simple additions, which is to take a Power BI report and put it onto a PowerPoint slide. So now it’s easy to make it part of a presentation story. You can have slides which are full of words and should stay slides, and then a slide that holds the data story part of your presentation. So it can all seamlessly on. You cannot do that easily using Excel. You can embed the chart, but not the whole interaction, the way the visuals work together. It’s there to an extent in Excel but as soon as you start getting clever, that’s when you should be moving into Power BI.

Michael Lonnon

What’s this showing you then that Excel can’t?

Laura Graham-Brown

In Power BI, I can click on a column in that chart, and it filters that chart over there and it filters that result over here. I can slice and dice the data, or anybody can slice and dice the data with a few clicks to show what they want to see. So quite often the people who want to find out answers in the data are not analysts, they are management, quite often. And as they’re not analysts, they don’t want to know how data hangs together, they just want to know the results. So actually, they need something they can point at and click without having to understand how Excel works. So as a tool to present data to people and give people – that magic phrase of data driven decisions – Power BI presents it in such a way that people get it. We can also establish refresh time. Because on Monday morning, you’ve got a report to do, is there ready for them? Excel? Yes, you can, but somebody needs to go into Excel and press the refresh button. Power BI, it’s got all refreshing sorted. It’s all there and we can build one fantastic dataset. It’s got all the nice measures all the right things in it. It’s all related together by the analyst. Okay, so there’s the guys that understand how this data hangs together, and then can come along the people who are willing and who know what they want to see. They know what they want and what they don’t want in their report, and they can do the visualisation so they can connect up to that well-built dataset and build their own report. Excel has been here a long time, it is a mature product, its fantastic, and it’s not going anywhere. It’s here to stay and lots of businesses live on it. They can in Excel quite easily go, well, that data that’s in that data set I now want to put into a pivot table. I want to explore it in a way that I’d explore data in Excel for your data analysts who are wanting to pull the same data that the management are getting. So you can now move to having one source of truth. One data set everybody’s reporting on rather than everybody doing a download into their own Excel file, which is now out of date. Even if you are going through dynamics data, the opportunities that are appearing change daily, you want to make sure your data is the most up to date version. That is important.

Michael Lonnon

That’s the problem, isn’t it with Excel, it’s not going anywhere. Organisations will still get value from it and can still get value from it. The problem is people downloading it, creating silos of it, using it in different ways that it wasn’t really what an organisation wanted to use for, it can create a bit of a mess can’t it if it’s not used properly. In Power BI it’s trying to get rid of that mess and create a single view.

Laura Graham-Brown

It’s the two halves of Power BI. There is the presentation, beautiful part there but then there is the back-end part of it, which is creating the datasets. Yesterday at Microsoft build they announced datamart’s which is a really nice way of presenting data, but preparing data with relevant security, or lots of nice things in there, that people can connect up to. It’s in preview, but that is going to change again, where Excel gets some of its data from rather than it is going back to the original data sources, connecting up to your SAP, your dynamics, etc, It will connect up instead to datamart’s or data flows or data datasets and pull data from there. So that everybody’s working off the same data rather than someone doing an extract.

Michael Lonnon

Who would use it then within an organisation, is it a technical tool? I’ve seen it and I’ve used it, I kind of have my own thoughts on it but where does it sit within a business? Who owns it? Is it the technical side, is it the business side, because ultimately, you’ll see insights on the business but there is a bit of a technical element underneath it, what do you think?

Laura Graham-Brown

There is a technical element underneath it all and an organisation should be looking at having a centre of excellence for their Power BI. So, there is a core of people who understand the back end of it, making it work efficiently to do the best things it can. But also, the people who are the customer of that is the business. So, your marketing team, your sales team, your HR, your techs, your devs, production lines. I had the reports built by guys who worked on factory lines, because they knew what they wanted. They had a report on their production line, they knew what they wanted to see, at three o’clock in the morning, when they are on 24-hour nightshift. They know what they needed to see better than I did, I had no idea what needed to see at three o’clock in the morning because I’ve not done that job. So that’s where there needs to be a link between your tech expertise to make sure Power BI is running smoothly. But also, so people actually understand what it needs to do, what I need, the information I need to know, for whatever job I’m doing and wherever I am.

Michael Lonnon

You’ve made it sound, that it’s not just accessible for most people in business, but you can also access it and use it pretty simply. In your opinion, I know as an expert it’s difficult for you to say, but how easy is it to create something in Power BI?

Laura Graham-Brown

I’ve taught Power BI quite a lot, so I’ve dealt with lots of people who are using it for the first time, and we’ve done dashboards in a day together. I think it’s possible to get good results quite quickly and quite easily. I think as soon as you get on to more complicated stuff inside DAX, or complicated calculations and measures, then yes, you need somebody with expertise stepping in to help. People can get that expertise, there is an amazing community out there who produce an amazing quality of Power BI work. Guy in a cube, SQL BI, and lots of other people. There are hundreds of other people out there producing content daily to support people learning Power BI, so there is wonderful resources out there also Microsoft, can’t forget them as well. So, there’s lots of resources to learn from but there is a balance to understand that, actually, is what you are doing going to get results. To get complicated, advanced reporting, you do need to spend some time learning the skill.

Michael Lonnon

What I like about Power BI, and you mentioned it there, is the community side of things and you can for example, create a dashboard very relevant to your line of work your line of business, and if you’ve got it singing beautifully, you could release that into the community for others to take advantage of.

Laura Graham-Brown 09:48

Yes, Microsoft have a gallery set up in which you can submit some things on there. There are some fantastic templates on there. One of my favourites is the periodic table there is a guy that produced a Power BI report that looks like what you and I remember in chemistry lessons, if you remember back, I can remember back that far. All sorts of data there is it they put a whole range of things from ones about people’s lifestyles to the interaction between people in Pride and Prejudice.

Michael Lonnon

That’s a bit out there!

Laura Graham-Brown

Yes, you could say, but it’s possible.

Michael Lonnon

Am I right in saying that if you have Microsoft 365, and you can correct me on this, if you have Microsoft 365, then you have access to Power BI?

Laura Graham-Brown

Let me make it really clear on licencing, Power BI Desktop is free to download, you don’t actually have to have any licencing at all, to get Power BI desktop. The point in which you licence is the point at which you want to share that report with somebody else. So, you want to publish it to a workspace and then share it, and there are combinations of licencing that you can have. So, you need a Power BI pro licence minimum and that comes with E five, if you’ve got Microsoft 365, it doesn’t come with E three, which is one of those common ones, but a five it does come with. But if you need to add it, it’s $10 a month and if you’re not doing reports, save you more than $10 a month or save everybody more than $10 a month you’re writing the wrong reports.

Michael Lonnon

And you need help.

Laura Graham-Brown

That’s my view. The number of hours it saves, it’s worth the 20 people who need that report paying $10 a month it really is.

Summary

You don’t need to be technically minded build reports and get value from Power BI.

And you don’t even need to pay for it. The desktop version is free!

If you want to start sharing dashboards you’ll then need to start paying for it. But if the time saved in building, updating and refreshing excel reports isn’t saved then you’re probably writing the wrong reports.

Excel is and will likely be for a long time to come, a useful tool. And may organisations will continue to get value from it. But if you want to present data in a visually impactful way – if you want to automate reporting – and if you want to provide management with insight, Power BI wins.

Thanks for listening, until next time, take care of yourselves.