Visualizing motion in Power Map for Excel
This blog post is about the possibilities and the potential of the Excel add-in Power Map . Power Map is a 3D data visualization tool which allows you to analyze your data visually. Through the use of a map it is especially useful for data which is in correlation with location data. Additionally you can analyze your location-based data over time so that you can notice possible timebased change.
There are already many videos about the possibilities of Power Map on the internet, although most of them only focus on static locations, e.g. the development of the sales amount per subsidiary or the opening of new sites throughout a country over time. Unfortunately only the fewest of these videos show the possiblity to show motion over time.
Below I will show you two short examples.
Example #1: Container-Ships
In one of our showcases we used Power Map to visualize the waterways of imaginary container ships which sail along the German Donau. Due to the fact that these ships conveyed chilled products, it was nessesary to analyze the container temperature in realtime. We simulated a botnet attack on one of the cooling systems whose invection spreaded all over the other container ships of the company. Fortunately due to temperature monitoring, it was possibile to notice the infection immediately and update the software of the cooling systems shortly after, so that the temperature dropped very soon. Below you can find the according video:
Example #2: Traffic jams
For my second example I tracked my way to work on a quite busy day. With Power Map it is quite easy to see where the traffic jams got me. Although my illustration is trivial, analysis like that could be quite interesting for logistics companies for example. They could collect the GPS-data of their trucks and visualize this data to improve their efficiency by avoiding high-traffic roads.
Visualizing motion in Power Map is not that difficult. The most important part is to get the spatial data in correlation to time. The waterways data for example I got from here . My route to work I tracked with an app called „Run the map“. Probably many other tools have the same functionality but I like the fact that this one is able to export your data in KML format so that you can analyze your data easily. To create a sample like mine, you have to do the following:
- After you finish tracking you can send the data in KML-format to your email-address.
- Open the KML-file in Excel, delete all unnessesary columns and seperate longitude and latitude to two columns. Otherwise Power Map is not able to create a mapping between your spatial data and the map.
- Make sure your data is formated as a table
- Make sure you have PowerPivot and Power Map installed
- Go to PowerPivot -> „Add to Data model“
- Go to Insert -> Map -> „Start Power Map“
- Map your Longitude and Latitude columns to the corresponding spatial categories (Your labels will probably differ because I have a German Excel version ; ) )
- Click „Continue“ and select the heat map symbol
- Drag and drop your Datetime column to the corresponding area
- If you want to see overlappings like in the traffic jam example above, you have to set the time setting to „Data stays until it is replaced“. It’s not nessessary to insert data in the value field.
- In contrast, if you want to visualize your data in the way the container-ship video does, you have to choose „Data shows for an instant“. In that case I recommend to include data for the value field so that you can show changes over time, like a temperature change.
In my example the S1-Value column shows the temperature of ship 1 at a specific point in time.
Power Map gives you the possibility to visualize your data in a refreshingly different way than pure numbers could do. Additionally it is important to notice that the visualization is not limited to fixed locations but it is also possible to analyze entities in motion. On the other hand Power Map is still in an early state and many features which are normally taken for granted in similar tools are still missing. To mention a few it is not possible to change the content of a legend. Also proper customization of diagrams like in Excel are not possible. Nevertheless Power Map is worth a try if you have location based data and want to visualize it to get new insights which you might not discover using traditional table based approaches.