The Clearly Podcast

Excel and Power BI - BFFs?

Summary

If you’re after a clickbaity title - this is the one for you (our podcasts with “Excel” in the title generally get more downloads).

But, Excel and Power BI continue to be BFF’s for many of our users and as Microsoft develop more functionality that binds them together, that case will only get stronger.

We can get purist about data structures so when the term “Excel as a database” is seen or heard we can get a little sensitive. That said, we use Excel alongside Power BI in almost all of our projects so we wanted to talk best practices for doing so.

Some examples of Excel working beautifully alongside Excel are:

  • Excel reports from line of business applications in a SharePoint folder feeding a Power BI model

  • Proof of concepts. Quickly model and change data to make a concept work quickly

  • Quick and slightly dirty cleansing (recent example of a 20 year old business application where we didn’t want to mess with the database but needed to change a reporting structure)

So please forgive the clickbaity title but we believe, when deployed correctly, Excel and Power BI are BFFs.



If you already use Power BI, or are considering it, we strongly recommend you join your local Power BI user group here.

Transcript

Andy:
I want to start with a shoutout to our youngest listener, Jackson Smith, who's just three years old. His dad, Jason, plays our podcast for him, and Jackson calls me Uncle Andy. So, hello Jackson! See you this weekend!

Shailan:
Good shout out to Jackson!

Andy:
Let's dive into today's topic: Excel and Power BI - BFFs? This is a bit click-baity, but it's an interesting discussion. Tom, you came up with this, so take it away.

Tom:
Sure. We often talk about how Excel is still a primary data store for many businesses. While we encourage moving data to more robust systems like Dataverse or SQL, Excel remains prevalent. To leverage that data better, integrating it with Power BI is essential. Use Power BI to visualize data from Excel, making it a more dynamic tool than just relying on static Excel charts.

Andy:
Absolutely. Power BI's accessibility and ease of updating data sources are big pluses. Shailan, how do you see this in your projects?

Shailan:
In many projects, clients use Power BI for its functionality and visuals but still want Excel for detailed data. They like to link Excel to Power BI datasets to share and analyze data. Power BI and Excel complement each other well, especially when Power BI models the data and Excel displays detailed figures.

Tom:
Excel often does a better job displaying large tables of data than Power BI. For detailed analysis, like in finance, Excel's cell-based format is preferred over Power BI's visuals.

Shailan:
Exactly. However, when departments create dashboards within Excel, introducing Power BI can provide more interactivity and better visuals, enhancing their analysis.

Andy:
I've seen great results using Excel, SharePoint, and Power BI together. Users can update Excel spreadsheets in SharePoint, refresh the Power BI model, and instantly see updated data. This setup works well for daily or even multiple daily updates.

Tom:
Also, for massive CSV files exceeding Excel's row limit, loading them into Power BI and connecting to Excel for further processing is a great solution. It bypasses the need for more complex data storage systems initially.

Shailan:
Right. This approach is useful for prototyping too. Using Excel to test data and scenarios before moving to a more permanent solution can save time.

Andy:
However, be cautious about using Excel for heavy data calculations and transformations. It's better to use it for raw data storage and let Power BI handle the heavy lifting.

Tom:
Yes, using Power BI just to export data back into Excel can be counterproductive. Evaluate if Power BI adds real value or if the task is better suited to the source system.

Shailan:
Training is crucial. Users need to understand the benefits of Power BI over Excel. Proper training ensures they can leverage Power BI's capabilities fully.

Andy:
Absolutely. Ensure that people mentally separate Excel and Power BI and understand how they complement each other.

Tom:
Understand your use cases and make decisions based on them. Don’t default to using Excel and Power BI together without a clear reason.

Andy:
Excel is excellent for quick updates and data storage but avoid using it for extensive calculations. Keep the data clean and simple.

Shailan:
In summary, use Excel for data analysis and Power BI for visual analysis. They should complement each other, not overlap unnecessarily.

Andy:
Great points. Next week, we'll discuss social media. Thanks for tuning in. Goodbye!

Shailan and Tom:
Bye!e, for listening. Goodbye!