The Clearly Podcast
Migrate from Excel to Power BI
Summary
In this episode, we discuss our experiences of helping customers to migrate from Excel to Power BI.
We talk frequently about how much Excel we see being used for data storage, manipulation and visualization when we start working with new clients. Furthermore, we talk extensively about the risks of proliferation of those spreadsheets, especially when they're replicated and changed by multiple users within the organization.
So how do you move your users from Excel to Power BI? It's not as simple as merely moving the spreadsheets into Power BI models, the trick is stopping users dependency on Excel as a business tool, moving them to use structured business systems linked to Power BI where they can get a universally understood source of the truth.
When doing this it's essential to understand your users motivations and dependancies on Excel so you can provide better solutions to their reporting and data needs. We work with the contention that most users don't want to be Excel formula experts so they're more satisfied when you give them better alternatives.
You can download Power BI Desktop from here.
If you already use Power BI, or are considering it, we strongly recommend you join your local Power BI user group here.
Transcript
Andy:
Hello everyone, and welcome to The Clearly Podcast, episode 22, "Migrating from Excel to Power BI," recorded on March 17, 2021. I'm Andy, and today, Shailan, Tom, and I will discuss the transition from Excel to Power BI. We often encounter clients who rely heavily on Excel spreadsheets, which can introduce significant business risks. We'll cover the signs that indicate a need for migration, strategies for moving to Power BI, and details about our training course to help with the transition. How are you guys doing today?
Tom & Shailan:
Good, thanks!
Andy:
Great! Let's dive into today's topic. We often see customers using Excel for data storage and visualization, leading to issues with uncontrolled proliferation of spreadsheets, inconsistent calculations, and unreliable KPIs. Shailan, can you talk about what this looks like in an organization and the signs that indicate a problem?
Shailan:
Sure, Andy. One of the main signs is when people question the numbers presented in meetings or have different versions of the same data. This happens because there is no central repository, and data is often emailed and modified across departments. Another sign is when Excel is used not just for storage but also as a visualization engine and even as an application. These can look impressive but are risky and unstructured.
Tom:
I agree. Another red flag is when management information is primarily presented in Excel. This often means data is static and not connected live to a database, leading to issues with data accuracy and integrity.
Andy:
I recently spoke to a customer whose board meetings often involved debates over data accuracy because of different data sources. This is another sign of a problem.
Shailan:
We could run a poll on how much time is spent in meetings discussing data validity.
Andy:
That's a great idea. Now, let's discuss the steps for migrating from Excel to Power BI. Many clients have structured business systems but still rely on Excel for data handling. The first step is to connect Power BI directly to these systems instead of using Excel as an intermediary.
Shailan:
Exactly. Instead of starting with a full BI project, begin by using Power BI to connect to your line of business applications and perform data transformations within Power BI.
Andy:
Yes, this weans people off spreadsheets gradually. Most professionals don't want to be Excel jockeys. Recreating their Excel reports in Power BI can show them the benefits of a more structured approach.
Tom:
However, be cautious. If you have multiple systems, you need to ensure your Power BI model can handle this complexity without replicating the issues you had with Excel. Think about processes that maintain data integrity and scalability.
Andy:
Absolutely. You need to establish organizational metrics and KPIs that everyone can rely on as a single source of truth.
Shailan:
Right. Using Excel as a data source in Power BI can introduce challenges. Excel is often used for both storage and application purposes, which Power BI isn't designed for.
Andy:
Power Apps can be a great tool here, providing a structured way to capture and manipulate data before visualizing it in Power BI.
Tom:
We should also consider how Power BI and Excel can complement each other. Sometimes, connecting Excel directly to data sources can solve the problem without fully migrating to Power BI.
Andy:
Power BI offers far more interactive and engaging visualizations, which can be a strong motivator for users to switch.
Shailan:
Agreed. For highly interactive dashboards, Power BI is superior, although Excel can still be useful for certain financial models and static data scenarios.
Andy:
SharePoint can also play a role, allowing you to store Excel files and refresh Power BI models automatically.
Shailan:
To wrap up, our top tips for migrating from Excel to Power BI:
Shailan: Don't just transfer data from Excel to Power BI. Go back to the source data and build your model there.
Tom: Ensure your staff are trained in Power BI basics. These skills are also applicable in Excel.
Andy: Take users on a journey. Start by recreating their Excel reports in Power BI to demonstrate the benefits.
Andy:
Lastly, we have a training course designed to help users transition from Excel to Power BI. It's a five-day, one-hour-per-day course, priced at £25 per module, led by Tom. The course is live and interactive, with recordings available afterward.
Thank you, everyone, for listening. Stay safe and have a great week!