Power BI: Microsoft Excel PowerQuery, PowerPivot, PowerView and PowerMap

There is a part of Excel most users don't know exist. It is often hidden till you enable the add-ins for it. It is Power BI. The collection of tools that help you work with large data easily, connect to multiple data sources, bring all the data into one report, do analysis on them, make a visually attractive dashboard and even create a Map (with a story-line/tour).

image: planetlotus.org

In a series of posts I will be introducing you to this powerful tool in Microsoft Excel. It used to require Microsoft Excel 2013, but now you can download the PowerBI tool separately and use it as a standalone program. Visit https://www.powerbi.com/ to download the PowerBI tool.

The PowerQuery helps you connect to almost any kind of data source, including another Excel file and a MySQL database. It let's you run queries on the data. I have used if for a report I make for a client from a huge live MySQL DB. And it works fast and fine without me needing to install any ODBC driver or 3rd party tool (MySQL for Excel).

PowerPivot is just like the regular PivotTable, just that it allows you to pivot data that has millions of rows (even hundreds of millions of rows). Something PivotTable can't do.

Then PowerView lets you create a data model. You are able link data from different sources. It's the equivalent of creating primary and foreign keys in DBA. So you can show more insights than was posssible out of the box previously in Excel.

PowerMap lets you geo-map your data. As long as there is a location field (state, long/lat) you can plot your data on a very beautiful interactive map. You can pan around, zoom and create a storyline or tour. It perfectly turns Excel into an amazing presentation tool.

And the best part of this all is that you can share your dashboard with someone else (maybe your boss). So they can view the most recent one you made at anytime without having to bug you to send a new report. They can even do some of their own analysis on the go in the dashboard without the rights to alter your underlying data.