The Clearly Podcast

Clearly Podcast Episode 1. Why Power BI isn't Excel with Shailan Chudasama, Tom Gough and Any Clark recorded on 8th October 2020.

Andy

Here are some of the misconceptions about Power BI

• Power BI is Excel on Steroids

• If I can do it in Excel it must be possible in Power BI

• Just think of Power BI as Excel and you'll be fine

As Power BI consultants we need to reset an expectation about why Power BI is not Excel.

Tom

The quote about Power BI being Excel on steroids has some merit because people tend to think about Power BI as an extension of Excel which it isn't. The "on steroids" is based on having a lot of features that exist in both applications through Power Query and the ability to import data from a variety of sources into either application and then visualize the data. However where the real difference comes is looking at line level data which Power BI doesn't do as well as Excel, Power BI is stronger at looking at aggregation and interactivity. It has far better data exploration tools that Excel - it's not about looking at every row of data.

Shailan

People use Excel as a database or as data storage. When people have a need to quickly store data they create an Excel spreadsheet which can quickly proliferate around an organization. Power BI isn't designed to work that way for storage.  One of the clear differentiators is that Power BI is a data analytics and exploration tool, although there is a historic overlap after Microsoft created Power View (and to some extent Power Query) within Excel as an add-in which had some of the visualizations that Power BI still has now.

Andy

So what about the point that people think if they can do something in Excel, they can do it in Power BI?

Tom

You need to start with the converse that if you can do it in Power you should be able to do it in Power BI. These are 2 separate tools for specific reasons - you don’t try to put a screw into a wall with a hammer. The tools have their purposes. You need to focus on what you're trying to achieve and then focus on the tool you need to achieve it.

Shailan

Companies often use Excel to capture and store information - budgeting and forecasting is a good example. We enter data, use visuals and track whether we're on target or not. The first consideration is how do you share those visuals from Excel? Frequently this is done through PowerPoint but the Excel file also gets emailed around the organization and is purposely or accidently changed leading to multiple versions of the same file. That Excel spreadsheet could also be put on a SharePoint location and accessed then potentially changed by many people.  Now, we can get Power BI to access that Excel data where we can very easily share that information.

Tom

There is a data-basing element of Power BI that can be used alongside Excel that makes them very complementary tools that provides different layers we may wish to analyze.

Shailan

We often hear from clients that they've done too much with Excel and it's caused them challenges as they have too many spreadsheets and want to switch to Power BI, but you can't just switch off Excel as it has its purpose within organizations.

Andy

Where does Excel have purpose over properly structured databases based on rows and columns rather than just cells?

Tom

The master store should be in a proper database such as SQL or you could utilize something less structured such as Mongo DB, etc. All of these options provide a much more structured and disciplined form of data storage but they don’t easily lend themselves to doing calculations and further analysis on you data which is where Excel still has it's place when you're trying to work out scenarios. For example, if we want to look at a scenario when you (e.g.) increase prices by 10% across all products Excel is a perfect tool as we don’t update the master data, we just want to look at the potential ramifications of making a business decision. That data can now be imported into Power BI with visuals created to understand the data in an aggregate form. But there's still a need to drill down to line level data where Excel has its place.

Andy

So Excel is a great tool for scenario analysis. Have you ever been in a situation where you steer a client away from using Excel as a data source?

Tom

Most of the time in an implementation you'll hit that point. It's not always feasible to do - most of the time you'll see Excel still used for budget data as it's something that doesn't get changed very often and customers don’t see the value in buying a dedicated system to manage the budgeting process. The cost/benefit of implementing a proper budgeting system can be a difficult case to make. When it comes to day-to-day operational data most companies have moved away from using Excel but you do still see it. In most cases when we're working on a Power BI implementation it's because the company is moving away from Excel into something more structured, so there's an appetitive to move away from Excel.

Shailan

We don’t try and stop people using Excel but we do try and provide audits. In a recent project we had a customer that was storing a lot of sensitive information in Excel and we worked with the customer to help them understand the security risk they were exposed to. Although the data was stored on a secured SharePoint, the data was still accessible and being emailed around the organization. Excel is a great tool but can be easily emailed and changed. You then have multiple versions so you have a question about which file is the actual source? We try and advise that Excel has value but if you need a central source of information then use Power BI where you can export Power BI data to an Excel spreadsheet but that's still better then sharing original files as the data has come from a central source.

Andy

There's an aspect of this debate where some of the roots of Power BI have come from Excel.

Shailan

Power BI's roots are from the Microsoft acquisition of a product called ProClarity (April 2006) and the creation of functions in Excel such as Power Query, Power View and Power Map. They had power tools that could be enabled as an add-in to Excel where you had to enable Silverlight to run them and but many organizations didn't allow Silverlight. Then Microsoft decided to introduce a product which is the geneses of Power BI, based on the ProClarity tools.

(Power BI was first released on July 24 2016 https://en.wikipedia.org/wiki/Microsoft_Power_BI#:~:text=With%20time%2C%20Microsoft%20also%20added,public%20on%20July%2024%2C%202015.)

Andy

If you have a customer that has issues with Excel and want to make the leap to Power BI, what can you do to help them on that journey? What are the top 3 actions someone can do to give themselves solid, robust reporting?

Tom

1. Move the main data storage from Excel to a more robust data source (e.g. SQL)

2. Understand where you still need to use Excel and where Power BI can give you an advantage

3. Look at other tools that are neither Excel or Power BI for delivery of some of the Pixel Perfect reports you may require in the business

Shailan

Look for specific use cases. We've seen many instances where Excel is still used such as management information - board packs and reporting. This is often re-created on PowerPoint or a PDF for sharing and in this use case Excel works well. But we found that some of that underlying data resides elsewhere . Then having Power BI producing that management pack it's far easier to share for visual analytics. As we can control data refresh for the underlying model the customer doesn’t have to spend time every month re-creating the reports. We had a particular example for a health and safety board pack where the days spent in production were increasing every month. It got to a point that the health and safety report was taking 8 days every month to produce. We helped the client and built a Power BI report from source files that are refreshed automatically. Because the data is refreshed often they don’t see this as a "monthly" report - it's a report that's regularly refreshed.

Andy 

There are some important points - the time saved in people producing Excel reports with the potential for error and giving a customer reliable reporting that's refreshed on a regular basis. When customers realize they can move to a reliable, robust and secure reporting environment they are taking a significant step forward.

Shailan

Increase in quality and decrease in errors by moving from Excel is important. Even if the customer is using Excel and getting data from source, Power BI gives an advantage. Even if there are errors at source, the rule is to fix the error at source but what happens is they use Excel to fix the error because it's easier to do that.

Tom

You need to careful of using the Power Query elements of Power BI to fix the errors upon import as all you do is move the error fixing from Excel to your reporting system and you never go and fix the real error.

Andy

Any thoughts on obvious places / resources to help someone move Excel assets to Power BI ?

Tom

Download and start using Power BI desktop which is a free downloads (https://powerbi.microsoft.com/en-us/desktop/). 

Look at Microsoft's own Power BI tutorials (https://docs.microsoft.com/en-us/learn/powerplatform/power-bi)

Join a Power BI user group which are a fantastic community for engaging with other users (https://www.pbiusergroup.com/home)

Shailan

Download Power BI and learn the tool. With a huge caveat - if you're used to Excel don’t approach Power BI the same way.

Our websites have lots of content that may be useful  (www.clearlycloudy.co.uk & www.clearlysolutions.net)

Tom

No-one is taking Excel off of you - it's not an either / situation.

Shailan

Clearly Cloudy offer a free audit so please contact us to discuss.

Andy

We'll follow-up with a podcast on Power Audits and a training course for people used to using Excel and how they transfer to Power BI.