Ad-hoc Analysis - Power BI or Excel?

Since Power BI Desktop is a free application, why are we still using Excel to make analysis?

That is a good question, and to answer it, I'll organize some points by type for reflection.

Naturally, you could have a different opinion or would like to put other reasons to use one or another solution, so please put it in the comments. I could add your contribution to the article.

Origins

Excel is a spreadsheet, and the first software like this was Visicalc, launched in 1979 for Apple II. Curiously, the first version of Excel was launched in September of 1985 for an Apple Computer too, the Macintosh. Just in November of 1987, Windows users started to use it.

It means that the concept of a table with lines and columns where you can put labels and associated values that can be used in calculations is old.

Of course, I'm not comparing the first version of Excel with the current one, but the basic concept, the features that maybe 90% of users use in their day-by-day activities, are these.

Important to say that Excel is a paid application.

Power BI is Excel's son.

It was formally launched as an independent application in 2015 as a free desktop application, and stays that way, but it was introduced as Power View in 2011 and in 2013 as a cloud-based service. I started to use it in June 2016.

It means that we are talking about 30 years of difference and a lot of new technologies and concepts adopted.

Today, Power BI is much more than a visualization builder tool. It is an entire ecosystem integrated with Azure and Fabric Services that put the product on another level.

Concept

Despite Excel's multiple uses and actual features, it is still a spreadsheet program. It is a simple basic concept but a powerful way to conquer a legion of fans.

Today, it is impossible to manage a business or the personal life without a spreadsheet (it could be done in a paper notebook, of course), and in this territory, Excel is king. And you can note its power when you remember that it is possible to connect it to a data source and use its versatility to analyze data, scenarios, etc.

Power BI is different. It supposes the existence of a data source unless you start typing all data using the not-exactly convenient small window available to create tables manually.

Power BI has two layers: Transform Data and Visualization Builder.

On Transform Data, we have the power of Power Query M program language, used to create a complete ETL process for multiple data sources. On Visualization Builder, we count on the versatility of the DAX program language, mainly used to develop metrics and make any calculations. In both, we can use external languages such as R and Python.

Creating a Power BI dashboard is easy, flexible, and a significantly better experience than using Excel graphics and tables. The context filtering is a big advantage. You start to understand your data almost instantaneously.

Analyzing data

Then, we come to the proposed theme. What is better for ad-hoc data analysis, Excel or Power BI?

This is a trick question. Is there a better option or just options?

I prefer to use Power BI. I usually take the data (it could be an Excel spreadsheet, a database connection, a web address, etc.), import it into a simple model, and analyze it.

I love the power, flexibility, and how easy is to create related different types of graphics, tables, pivot tables, etc., all with native filters. Everything looks easy and fast.

With Power BI and your data management tool, it is extremely easy to relate multiple tables from different data sources, change data types, and analyze data consistency. Power BI has a really powerful set of tools for those who want to extract more from your data.

But..............................

Despite my preference, I love Excel too. It provides a more direct approach to data, allowing calculations, scenario simulations, and also pivot tables and graphics. I usually use Excel for tasks that do not involve multiple data sources, maybe because I never learned how to use the LOOKUP or VLOOKUP functions properly.

There is a mantra that I repeat all the time:

"Your idea is great on PowerPoint, but let's see if it survives on Excel"

I believe that this sentence demonstrates how important Excel is for everybody.

Conclusion

Excel is a great tool with thousands of uses, but it is necessary to respect its concept age, and limitations. It is not a good idea to use it to analyze 100k or more records.

The limits of Power BI touch millions of records, and it is considering the "import data" access method. We have the "direct query" option for more.

And, if you came here and are thinking about the Power BI complexity, remember that to extract more from Excel, you also need to study a lot.

It is a matter of deciding which technology you will invest your time to learn.