For many years, Excel has been the most widely used data analysis software for most businesses. However, in recent years, you may have become aware of Power BI, which is another newer Microsoft product. You might feel that Microsoft is nudging people towards Power BI and wondering is it right for you. In this post we’ll see what product works for what groups of people.
Overview of Excel
You already know what Excel does, so we won’t dwell on it too long here. It’s a spreadsheet application that is used for storing and analyzing data. The data can be created in Excel or imported from another data source. Data is generally analyzed using formulas, and displayed using outputs like charts or Pivot Tables.
There are very few limits to what an Excel spreadsheet can be used for, beyond the imagination of the spreadsheet designer. This can have positive effects. For example, this versatility is probably why Excel is so widely used in businesses today. However it can also have negative effects. Often, Excel sheets will be used to accomplish tasks that are really not suited to Excel.
Overview of Power BI
Power BI is a self-service business intelligence tool, focusing on visualization. What this means is that Power BI allows you to import data from a wide variety of sources, and create a series of charts analyzing that data. These charts can then be combined into reports and dashboards that provide insights into your business’ data.
Power BI is accessible to audiences without significant technical skills. Although it has its own formula language, it is possible to create charts and dashboards using a simple drag and drop interface, without needing any coding knowledge. In this way, Power BI broadens the range of people who can produce compelling visual insights.
The Power BI brand incorporates several different products. There is a desktop application letting you import your data and create. There is an online service, where you can upload report, create dashboards, and share content with users inside and outside your organisation. There is also a Power BI mobile app, letting you and others view Power BI content on their phones.
The intersection: Power Pivot and Power Query
It’s not well known to a lot of people, but many of the capabilities of Power BI are actually available in Excel. Specifically, there are two common tools that make up the backbone of Power BI: Power Pivot and Power Query.
Power BI allows you to import data from various sources in order to analyze them. Data is imported into Power BI using a tool known as the Query Editor. This allows you to import data, transform it in various ways (for example adding and removing columns, adjusting data types, or changing the format of the dataset), then load it into Power BI. The exact same tool is available in Excel where it is called Power Query (in Excel 2010 or 2013), or Get and Transform (in Excel 2016).
When you import data into Power BI, it is imported into a data model. This is a structured model, where data is stored in tables. These tables can be related, and charts are assembled using data from the various tables that make up the data model. This differs from “normal” Excel, where data is generally unstructured, and can be spread over various rows, columns and ranges of cells.
However, Excel provides a feature called Power Pivot, which provides the same data modeling capabilities as Power BI provides, including the same structured data models, and the same formulas for analyzing the data model. Almost any data analysis that can be done in Power BI could also be done using Power Pivot.
Which should you use?
There are two questions you should ask when deciding whether to use Excel or Power BI
Does “normal” Excel meet my needs or do I need Power BI features?
If your data is generally unstructured, Excel may be best. Power Bi relies on data being arranged in tables, with clearly defined rows and columns. Excel lets you analyze almost any combination of rows, columns and cells. If converting data to a structured format would require too much effort, and Excel meets your needs, then the effort of structuring the data set.
If your work involves no data analysis, then Excel may meet your needs. Power BI is designed to analyze data, primarily using charts and dashboards. For example, if your Excel work primarily involves putting data sets together, and not then analyzing them, then Excel may be enough to meet your needs, possibly using Power Query to import data from various sources into Excel.
Of course, there is nothing to stop you putting together a dataset in Excel, then analyzing it using Power BI. In fact, this would be a common workflow in many situations.
Should I use Power Pivot or Power BI?
If visualization is a priority, use Power BI. The data modelling capabilities of Power Pivot and Power BI may be the same, but the outputs differ considerably. Power BI offers a wide, and expanding range of charts that can be used to explain your data, while Excel and Power Pivot limit you to Pivot Tables, Pivot Charts and standard Excel charts. As useful as Pivot Tables are, the range of visuals available in Power BI is likely to be more useful for most people.
Power BI is also easier to use for non-technical users to understand. Power BI is designed with such users in mind. For example charts are created by a simple drag and drop interface, with no formulas needed. Power BI also provides various features designed to generate analysis and insights without requiring extensive technical expertise. While Power Pivot provides all the same functions, you’re more likely to create formulas of your own, and generally do a bit of coding, in Power Pivot than in Power BI. For that reason, Power Pivot is likely to be of interest to more advanced users than Power BI.
Finally, Power BI may be better if you want to analyze your data, and then share it across your company. Power BI’s online service has several features that are designed with sharing and collaboration in mind. Sharing a Power Bi dashboards can be much easier than sharing an Excel workbook.
If all your data currently lives in Excel, and you’re not currently using Power Pivot, you could start by using that. The Excel interface might be easier to learn if you already “know” Excel, and you can easily move to using Power BI later on if you feel you need the visual and dashboard capabilities.
On the other hand, you can start using Power BI for free, while Power Pivot is only available in more high-end versions of Excel, which your company might not have. If price is an issue, or if your company’s Excel version does not include Power Pivot, then Power BI may be the better option.
Conclusion
Power BI is undoubtedly a big improvement on Excel when it comes to analytics products offered by Microsoft. However, Excel has more to it than many people realize, if you use the Power Pivot and Power Query tools available there. However, with these tools only in a small number of Excel and Power BI available for free, I think Power BI is the way to go if you are interested in a Microsoft self-service BI application.
