This tutorial describes how to build a self service ETL solution with Excel 2013 Power Query to gather climate data from different public sources and visualize them with Power Map.
The following animation is the result when you render out the story from Excel. It illustrates the intensity of tropical storms in the west pacific from 1945 to 2012. In addition to that it shows the last big typhoon "Haiyan" that hit the philippines. The green bars indicate the estimated affected Population. You can also see the flood simulated in blue color as well as more than 400.000 twitter messages related to this event in yellow and red.
The entire data collection, ETL, simulation as well as the rendering is done in Microsoft Excel 2013 with Power Pivot, Power Query and Power Map Features.
There is no additional screen-capturing software required. You can simply save your Video as well as the following Screenshots out of Excel.
As we continue to face extreme weather conditions around the globe I would like to share a quick demo on how to build a climate visualisation based on various open data sources from governmental agencies and research labs. As we take data from the Internet I would like to highlight that there is no guarantee that the data is valid and/or trustworthy. Especially data collected between 1945-2000 should be viewed with scepticism.
Here are some sources:
Historic Windspeed Data: (Velocity in Knots)
Typhoon Haiyan Flood Data:
Typhoon Haiyan Windspeed Data (Velocity in Meter per Second)
Historic Weather Data
Moreover you can incorporate Twitter Data using the TweetSharp Nuget Package. (will be described in a separate Blog Post)