Microsoft Power BI Demo Contest

January 28th 2014

I entered a competition to show off the capabilities of Power BI in Excel 2013. Here is my video:

Vote for my video entry here!

Are those features in Excel?

Yes they are. What you're looking at was done entirely in Excel 2013 using the free Power Query & Power View add-ins. One caveat: you do need a more expensive version of Excel 2013; either Office 2013 ProPlus ($12 a month via Office 365), or Microsoft Office Professional Plus 2013 (which is not sold at retail, but is available via volume licensing to businesses and their employees).

What is Power Query?

Power Query is an ETL tool. It essentially allows you to import data into Excel from almost anywhere without copy & paste. It can extract data from tables on websites such as Facebook and Wikipedia (even when the table only shows 10 rows per page), it can import data from databases or flat files either locally or on the web.

You can also set up rules during the import - for example, if you only want to import certain rows, or if you want to name the columns. You can even go as far as unpivoting the data (something that can be incredibly hard to do in Excel).

Importing data with Power Query

Power Query: Why not copy & paste?

Copying and pasting is fine, except that it can only be done once. If you paste data into Excel and then do a little manipulation (such as deleting columns, adding column headers, etc.) all those manipulations are one-time only. If the data updates next month, you have to follow the same steps next month. Power Query allows you to automate all those menial tasks.

What is Power Pivot?

Power Pivot is a modelling tool. Think of it like a table of data in Excel. The difference between Power Pivot and regular Excel is that you can link 2 tables together without using VLOOKUP. You can link 20 tables together in the same way. You can also do complex calculations: with a calendar table you can easily do YTD or MTD or MTD LY or YTD LY calculations - something that is very hard to do in regular Excel.

Relating tables with Power Pivot

What is Power View?

Power View is a data exploration tool. It is essentially interactive charts and maps. Rather than the static charts that appear in Excel, the charts in Power View interact with each other. If you click on the 'Wisconsin' bar of one chart, all the other charts will filter to Wisconsin. This allows you to explore your data and see trends without having to pre-build charts for every state.

Mapping with Power View

What is Power Map?

Power Map is a map-based presentation tool. It allows you to add data to maps and then create a presentation of that data that you can walk through with an audience. It's great if you want to demonstrate something map-based as part of a quarterly update or annual meeting. I did not use Power Map in my video.

What is Power BI?

Power BI is all of the above Excel components, plus a private website where you can upload data models (Power Pivots) and data visualizations (Power Views) for consumption by others (either via the website or via a mobile app). This saves you having to email around Excel files and allows everyone to consume their data in the same place. It also comes with features such as Q&A (where you can ask a plain English question such as "How many tornadoes struck Wisconsin between 1950 and 2012" and get a chart in response). Unlike the free Excel add-in's above, Power BI sites do have a cost associated with them.

Behind the Scenes

The focus for my tornado video was to demonstrate a small portion of what Power BI is capable of. It is not a training video that walks you through step-by-step, but rather an overview of what can be done, designed to whet the appetite. There are a number of boring but necessary steps I left out of the video, and indeed some of the visualizations I demonstrated were ones I had already built. For that reason, I don't have a single file of my finished work that I can share.

My decision to enter the competition was a last minute one. At the beginning of January I decided I would enter and worked on it for a few hours. Then I didn't do anything until deadline day, when at about lunchtime I decided I may as well submit something. I then worked on the video until the deadline (7pm CST). For that reason, you may notice the production values of the video drop as I get towards the end: I didn't have time to watch the video before I uploaded it. I literally threw on a final slide and started exporting and uploading it. I got the video in 3 minutes before the deadline, which was certainly cutting it closer than I wanted to.

Given how little time I actually put into the video, I actually feel pretty good about it. I would have liked to do something with Power Map and with Q&A. But, overall and in a short amount of time, I feel like I did communicate some of the best features of Power BI (including mapping).

My Personal Take-Aways

  1. I did not realise how powerful Power Query was until I used it as part of this demo. For folks outside IT who only have access to tools like Excel, Power Query is revolutionary in terms of automating data imports in one step.

  2. Video is a great way to quickly demonstrate what you can do with a tool. I'm tempted to create a whole series of short videos about Business Intelligence and Power BI that I can use when I'm trying to explain what it is these tools can do and why they're so powerful.

  3. I love Camtasia Studio. It is head & shoulders better than any other video editing software I have used. Unfortunately the license is $300, which is expensive. I would love to buy a copy for myself, but $300 is far outside my "just buy it" price range. I shall have to think long & hard about what videos I would create and whether it would be worth the $300 price tag to create them.

  4. It's always better to be in the competition with a less than perfect entry, than not be in the competition at all.

***You can still vote for my video entry through Thursday January 30th 2014. Please do so!***

Go back...

Visitor Comments

Post Your Own Comment

Note that comments may not appear immediately on this site.

Commenting is not available in this channel entry.