Project Botticelli

Introducing Microsoft Power BI Purchase this course

16 September 2014 · 3 comments · 2771 views

Power Query, Power Pivot, Power View, Power Map, Power BI Sites, Q&A, plus Hybrid

Rafal introduces the components of Power BI and Excel BI

This in-depth 1-hour 15-minute video introduces Microsoft Power BI which deliver advanced self-service analytics to everyone. Building upon the most popular analytical tool on the planet, Microsoft Excel, and its recently released key BI functionality for acquiring and preparing data (Power Query), modelling and enriching it (Power Pivot), visualising it (Power View), and geospatial reporting (Power Map) you get a powerful set of business analytics tools while still having the simplicity of Excel!

Power Query is a pretty able DIY data wrangling tool. It allows you to get data from a multitude of sources, including SQL databases (Microsoft, IBM, Oracle, MySQL, Postgres), the cloud, SharePoint lists, SAP Business Objects, Hadoop and its big data, or Exchange, Active Directory and Facebook. You can even scrape anything that looks like a table on a web page, by just providing a URL. Best of all, however, Power Query includes an online data search tool, which looks for interesting—and useful—tables of data on Wikipedia and many other publicly accessible sources. The demo shows you two ways to get data from the web, first using a data marketplace, and then again, from Wikipedia, where we search for population statistics of the USA. While you prepare data, naturally, you will need to make changes to it, like adding calculated or derivative columns, removing unnecessary ones, cleaning errors, pivoting or unpivoting data and really much, much more.

Once you have the data, you would normally start modelling and enriching it by adding calculations, relationships, or hierarchies. This is done using Power Pivot, which also looks after compressing your data and letting you analyse even millions of rows in the memory of your laptop!

Interactive reporting and visualisation is done using Power View. Rafal shows how to create an animated bubble chart from the data we have just imported and modelled in the earlier steps.

Sharing your analytics is done using Power BI Sites which is a subscription, cloud-based technology. It lets you share large workbooks (up to 250 MB in size as of today) with others through the browser, and they work on most devices, thanks to HTML5. You will see that a Power View shared this way looks and works just like the one we have created in Excel. If you are not ready for the cloud, you can also use SharePoint Server to provide for a good way to collaborate on workbooks containing data models or Power View reports. This is further discussed in our separate, in-depth videos—start with “Introduction to PowerPivot for SQL Server”. Whatever you decide, your BI will look and work the same way, no matter how you chose to collaborate, and you can always change your mind! There is much more behind the scenes, by the way, including the ability to schedule automatic workbook refresh even using data that comes from an on-premise databases. Thanks to the Data Management Gateway, hybrid scenarios give you the best of the cloud without exposing everything you have in your own datacentre.

If you decided, however, to use Power BI Sites as a way of sharing your workbooks, you would get an extra, very welcome feature: Power BI Q&A. It feels almost magical in how it is able to interpret natural-language, typed questions, and to answer them by showing a simple, accurate number, chart, or a map. You will see many examples of such questions in the demo, starting with something simple like “What is our pipeline revenue” and finishing with questions that tabulate revenues, sales cycle stages, product categories and even geographical locations.

The last BI component of Excel discussed in this video is Power Map. It is used for creating interactive geospatial reports, and it can even lookup and geocode your textual location descriptions, including addresses and names of known landmarks or buildings. It is fairly automatic to use, and it comes with a visually impressive mode of presenting your reports in which you take a flight over the terrain, moving from one city to another, perhaps, while a map showing your data as bars or a heat map moves beneath your feet. You can see this and also how to create a new Power Map in the demo.

If you would like to learn more about Power Pivot and Power View, make sure to review the existing modules of our SQL BI course, especially “Introduction to PowerPivot for SQL Server 2012” and “Introduction to Power View in SQL Server 2012 Reporting Services”, as the underlying technology of those components in Excel and Power BI is exactly the same. Naturally, to make a good use of the modelling features of Power BI, you should also learn Data Analysis Expressions, start with our very popular “DAX in Action!” video.

Log in or purchase access below to the premium version of this content.

Purchase A Full Access Subscription

Subscription Best Value


Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.


mnkashama · 2 January 2015

Very good presentation. Thanks Raf!

Rafal Lukawiecki · 2 January 2015

You are very welcome, Muamba, thanks for your kind words.

whytheq · 17 January 2015

Very interesting presentation Raf. I've not started to play with PowerPivot yet but have a feeling that I may be slightly frustrated as my work pc is 64-bit but our Excel version is 32-bit - I assume that will limit PowerPivot's capabilities?