The problem with spreadsheets & Power BI

We’re often asked to fix broken Power BI dashboards and in this video I wanted to talk about a common cause – Excel.

First, a bit of background

A LOT of management reports come from SPREADSHEETS. It’s super common. And it’s not surprising why people want to move their reports away from Excel. Because often, they take a long time to produce. Copying the data from various sources, manipulating and cleaning the information so that it’s relevant to the report. There’s a lot of work hours poured into these reports on a daily and weekly basis. And if you need that report now, the delay can be frustrating.

And there’s another issue. No matter how diligent we are, there’s always a risk of human error. We’re not robots, we all makes mistakes. Sometimes, you’re left wondering if the report is correct. Is something amiss in the data input that’s skewing the figures? Can you absolutely rely on the reports to make critical decisions? The term ‘rubbish in, rubbish out’ comes to mind.

The common answer

Not surprisingly, Power BI dashboards are often seen as the answer. Up-to-the minute reports that you can call up online, whenever you need them. Beautiful visuals that are easy to absorb, that’ll give you the answers fast. Sounds perfect right?

The common problem

But, there’s a problem – and we see this often. We see people start with the visualisation – diving straight into the Power BI dashboard, drawing charts and graphs from the existing spreadsheets. The old process is still there and somebody still has to manage the spreadsheets. Yes, you have a prettier way of displaying the information. But…

  • The work remains the same
  • The risks remain the same
  • It’s still just as likely to be wrong

In fact, these dashboards can make things worse because once they’re presenting in a slick manner, people assume that the data behind them is robust and trustworthy. And that helps no one.

The answer

So how do you fix that?

Well, you start with the source data and a concept known as DATA MODELLING. Find ways to automate the data collection – away from spreadsheets and into a central archive. Power BI has a suite of tools to help you do this. Power Query and DAX are where we typically start. Use these tools to first automate the data collection and then Model and structure the data into meaningful information for the dashboards to use. This often includes a little basic AI (Artificial Intelligence) too.

It’s all in the planning – making sure that the base data is correct and reliable, PERMANENTLY. It takes more time – sometimes a lot more – but it’s well worth the effort..

A little example

Let’s put that in context…A company needed to compare sales figures from Dynamics 365 against their monthly targets. Every time someone needed an update, someone had to manually export data from Dynamics into a spreadsheet – and then ‘fiddle’ with the information to get the right figures.

There was a large work hour cost and of course, risks of human error. Sound familiar?

Automation: The solution was to automate the spreadsheet step – to implement ‘connectors’ within Power BI to directly draw data from the Dynamics 365 instance.

Targets: The company target figures were built into this automation so that no ongoing manual interference was required.

Schedule: The data was updated automatically on an hourly schedule, so business leaders could see the latest position with NO extra effort from anyone.

Return on investment: In this case, it saves the company ONE WORKING DAY each month. More importantly, they’ve increased visibility and confidence in their sales data, and have embedded the sales dashboard right at the heart of their sales process.

Pay off: The planning, modelling and automation can save you future time, effort and expense, and restore trust in your reports. Ultimately, that’ll give you the insights and confidence to make the right decisions for your business – Just what Power BI dashboards should do!

Share This Post

Accelerate your Power BI delivery

Speak to a Power BI Developer today and we can help you get the most out of your Power BI dashboard.

Let's get things started...

Fill out this simple form and one of our developers will be in touch as soon as they can.

Download the pricing fact sheet

Enter your details below and click ‘Download’

Download training catalogue

Enter your details below and click ‘Download’