Power BI with Excel

Power BI is the name gives by Microsoft to a bunch of tools to create amazing dashboard.

Limitation of Excel

For many people Excel is a tool that is used for its formulas, graphs or macros. I can’t blame them, I’ve worked that way myself for years.

However, no one will have missed that an Excel workbook is very slow when there is a lot of formulas. Also, the charts are nice but they are not dynamic (no drill are possible) and neither interactive with other elements. And finally, macros are useful to create automation but when you have to maintain a code, it’s a nightmare.

Based on this observation, it was therefore necessary to create new tools that would make it possible to go beyond these limitations.

Power BI with Excel 2013

With the release of Excel 2013, a lot of new tools have been added:

  • Power Pivot (for the connectors and the calculation)
  • Power View (for the visualisation of the results)
  • Power Map (to place the data on a 3D world map)

These tools to do Business Intelligence were interesting but a crucial one was missing. It was a tool to manipulate and transform the data. This tool was added through an add-in in 2014. Its name is Power Query

So that is the problem with Excel 2013. There were tools dedicated to handling a large volume of data but the binder to structure the data did not exist in the native version. So it’s was nearly impossible to create dynamic dashboards.

Power BI with Excel 2016

With the release of Excel 2016, the weakness of Excel 2013 have been corrected in order to analyse your data in a better way.

In Excel 2016, Power Query has been added directly in the ribbon in the tab Data but the name has been changed. Now it becomes “Get and Transform”.

Honestly, it’s not a good idea to have changed the name. Power Query is really the best keyword to do research on the web. With the keyword Get and Transform, you find nothing.

Power Query has been added but Power View has been removed like it is explain in this article. To visualize the result of your data, you have the new charts (like sunburst or tree map).

Sunburst chart
Sunburst chart

But with Excel, the visualisation of the data is still old fashion. If you really want to create great dashboard with modern visual, the best tool is Power BI Desktop.

Power BI Desktop

Power BI Desktop is a free tool but it is not include in Office. You can download it from the Windows Store.

In both application, you find Power Query for the connectors and the manipulation of data and also Power Pivot for the creation of measures. But in the application Power BI Desktop, you have amazing visuals and also a lot of options to share your dashboard with your organisation or your group on a browser just with a URL address.

Also, you can directly insert your report in a webpage like you can see in this example. Click anywhere to filter your dashboard.

Leave a Reply

Your email address will not be published.