Combine Data from Various Sources with Power Query and Create a Killer Visualization with Power Map

I am sure that most of you BI enthusiasts have heard about the Power BI for Office 365 Preview by now, and today I am excited to show off some of the new capabilities in this ten minute video. Please watch to see what you can do with Power Query and Power Map, and then get your Excel 2013 version ready with Power Query and Power Map to get started. See the system requirements for other supported versions of Excel.

Here are the key strengths of Power Query and Power Map, demonstrated in the video.

Get data into Excel from a variety of sources

Access data from local files, HDInsight, Oracle, SQL, Access, DB2, MySQL, etc. This is just the beginning and we are constantly adding more. If you see a website that has a great table in it that you want to pull into Excel, use the “From Web” button on the ribbon in Power Query and simply paste in the URL. Power Query will walk you through the rest of it.

In my video, I connect to a Windows Azure HDInsight blob storage container to get data from the files stored there into Excel via Power Query. The files contain weather data from the year 2012, including wind speed. Latitude and longitude information is included in that dataset.

Share data with others

Depending on the size of your organization, you may be familiar with the role of a data steward. These are people who curate data so others can consume them. They know what the latest and greatest datasets are and they make sure that only the best and most accurate versions are published. In the first part of my video, I act as a data steward.

The second dataset I am sharing is the US airport arrival delay information. I also pulled that off of the same blob storage container, but I do not show this in the video as the steps are exactly the same as I showed before with the weather data.

Find shared data

Next, I switch to the role of an analyst who wants to complete the task of figuring out what effect the weather (specifically wind conditions) had on airport delays in the US in 2012. At this point, the data from the above mentioned datasets were published by the data steward and the analyst can simply find the shared datasets (Weather Station Data and Arrival Delays) via the Online Search button in the Power Query ribbon.

Create quick mashups

In this example, the analyst then realizes that the Arrival Delays dataset is not showing latitude and longitude. She has run into this issue before and she has a local file that contains the airport codes with latitude and longitude information, which she mashes up with the Arrival Delays dataset using Power Query.

Visualize geo data with Power Map

The last part is the most fun, visualizing the results with the new public preview of Power Map! She creates two layers in Power Map--the first one shows the weather data on a heat map and the second one shows the arrival delays as stacked columns, a very plastic display of the impact of windspeed on arrival delays in 2012!

Thank you for following along! This video was created with the help of many fine folks on the Power Query and HDInsight teams!