When it comes to Microsoft’s business intelligence products, understanding the basic terms can be complicated – especially if you are just diving into data & analytics. So let’s answer the questions which you had been afraid to ask such as:

1. What are the differences between Excel and Power BI?
2. When is it best to use Power BI and when should you rather use Excel?
3. What are the differences between Power Query, Get and Transform, and Query Editor?

***If you find this blog helpful, you can subscribe to our newsletter. If you need help with your data processing, shoot us an email.***

Excel vs. Power BI

The basic question. What are the differences between Excel and Power BI?

Microsoft Excel

Excel has been with us for a while – since 1985. It’s the tabular processor which everyone is using. Over the years it’s been updated many times and the current version (today is 2018-10-28) look like this:

Throughout this article, I am using sample crime dataset from FBI, you can get it here. 

Excel can serve you with many things:

1. You can do ad hoc calculations across random set of cells and tables
2. You can create pivot tables which can replace some of your basic database needs
3. You can create charts
4. You can install many extensions – some provided by Microsoft (e. g. Solver), some provided by 3rd parties (see some examples here)

Excel is an all-rounder. in fact it can be the only tool you will ever need to perform your data analysis when your datasets are fairly small.

Microsoft Power BI

Okay, now things get little confusing. When you say “Power BI”, it can be mean several things so let’s deconstruct all the meanings. According to Microsoft, “Power BI is a suite of business analytics tools that deliver insights throughout your organization.”. Notice the word “suite”. Yes, it’s not just 1 product. So what is the suit made of?

Microsoft Power BI for Desktop

Power BI for Desktop is an application running on Windows machines. Sorry Mac users. No version for Macs yet.  Power BI for Desktop is a tool which allows you to design your dashboards based on your database-like data structure = many rows and many columns. Simply said, imagine Power BI for Desktop as Excel which does not have any cells and which only allows you to add visual elements such as charts, various indicators or calculated metrics when it comes to analyzing the metrics.

Power BI for Desktop looks like this:

I told you – no cells in Power BI for Deskop! It’s a canvas into which you add visualization elements. When you know some basics, you can create something like this:

I am using simple table and chart visualization element. Again, I am using the same dataset as for the Excel example.

Power BI for Deskop is the app where you design your dashboards which can either sit in your PC or which can be uploaded to Power BI online service where it can refresh automatically if certain conditions are met. As I said, the data coming into Power BI Desktop must be at least slightly structured, forget doing ad hoc calculations somewhere on the side, it’s very difficult to make e. g. sums between 2 cells on different rows. The data just needs to be rows and columns and you treat it like database.

Power BI online service

When you are done with designing your dashboard, you probably want to have it refreshed automatically on regular basis. That’s when Power BI online service comes in. Power BI online service is a server which can do the automated refreshes for you. I am saying “can” on purpose – your data needs to be accessible online via one of the connectors which Power BI supports. This is a topic for separate article which is coming soon.

When you log into Power BI online service in your browser, you will see:

Then you locate the report which we just created:

Yes, it does look very similar to what you see in the application for your Windows Desktop, the difference is that this report now lives online and could be refreshed automatically if your data is reachable online. Since I am using a very simple dataset downloaded from FBI’s website to my desktop, my dataset is not refreshable via the online service by default.

When Should You Use Excel and When Power BI for Desktop?

It’s a simple question but the answer is more difficult. Excel in its pure form is good for ad hoc analysis and calculations when you don’t necessarily have database like structure. Assume this dumb example:

18 is the sum of the remaining numbers on the sheet. In Excel, it will take you 30 seconds to get to the sum of 18. In Power BI for Desktop, it’s nearly impossible to do it. So Power BI must suck, right? Not necessarily. Assume you have several million rows of crime related data from FBI in CSV file:

Okay, my screen is showing just 20 rows, but assume the input file has 20,000,000 rows. Will you able to process such data in pure Excel? No. problem number 1 is that Excel can carry only 1,048,576 rows on one sheet. Problem number 2 is that Excel gets very slow when dealing with hundreds of thousands rows because it caches everything into RAM unlike Power BI which “processes” every step but stays idle when doing nothing.

So when you have thousand of rows of structured data, go for Power BI for Desktop. When you need to do some random calculations, go for Excel. 

Power Query, Query Editor, Get and Transform

There is “one more thing”. Microsoft has decided to make matters worse and use 3 different names for pretty much the same thing. The “same thing” is the set of features both in Excel and in Power BI which allows you to clean up & transform your messy data. Imagine it as a “standalone” module in Excel and in Power BI which opens in a separate window and runs on the top of Excel and Power BI:

1. Power Query = Get and Transform – that’s how those features are named in Excel. It used to be called “Power Query” but it got renamed to “Get and Transform” in Excel 2016.
2. Query Editor – that’s how those features are named in Power BI.

The set of features allows you to perform ETL processes (extract-transform-load) such as:

1. Filtering only for specific rows
2. Getting rid of headers
3. Getting rid of bottom total rows
4. Unpivoting regular tables in order to make them look like a database tables
5. Adding custom columns based on IF conditions
6. Joining tables together based on the common keys
7. Appending multiple tables together
8. And much more…

After you are done with extractions and transformations, you can use the processed data as input for your pivot tables in Excel or as input for your visualizations in Power BI. In fact, when showing the FBI example earlier in the article, I had to get rid of the 3 top rows, and get rid of the notes at the bottom before I could load the data for visualization. That clean up process was done in Query Editor in Power BI.

Screenshots in Excel:

Then when you hit “Launch Query Editor”, a new window running on the top of Excel will open:

Now moving onto Power BI for desktop:

Hit “Edit Queries” and you will see:

When you compare the buttons in the query editor in Excel and in Power BI, you will notice they are almost exactly the same 🙂

Knowing how to use the query editor in Excel or in Power BI brings your analytical skills to whole another level – you will no longer have to suffer when trying to refresh messy data. You do your query once as stepped process and then you just keep refreshing it next time.

In this article, I mentioned “pure Excel” few times – that’s the Excel everybody knows with all its limits (like 1,048,576 rwos).  Pair it with Query Editor all the limits are gone. You can now load millions of rows into Excel and use them as source for your pivot tables.

***If you find this blog helpful, you can subscribe to our newsletter. If you need help with your data processing, shoot us an email.***

Conclusion

We hope we’ve helped you understand the basic terms around Microsoft’s analytical suite. More articles to follow!