One year ago, I was holding my wife’s hand as she completed the most amazing and difficult journey of her life so far: the birth of our son. We’ve each grown so much since then, it’s hard to remember how different we were before he was born, and it’s fun to look back over this last year to remember all the changes in his young life and in ours.
For fun I decided to commemorate his birthday with a visual tour of some of the places that he’s been. We have extended family in at least three states and two continents, and have had the good fortune to be able to visit many of them this year, as well as the chance to visit a few places on our own.
Here’s the video, created entirely using Microsoft Excel and Power Map Preview for Excel 2013.
NOTE: Workbook is included as an attachment below for your use — enjoy!
You may have noticed the orange animation effect highlighting the places that he’s been over time. That’s not an official feature of Power Map, but something that I added myself by doing a little extra work in Excel.
I started by figuring out the latitude and longitude of each of the cities that he’s visited. This is usually something that Power Map can handle for you. You just tell Power Map that you visited “Seattle, WA” and it finds Seattle on the map! But what Power Map can’t do (yet) is tell me all the points on a line between Seattle and Juneau, Alaska. For that, I needed latitude and longitude data in Excel, so I built a little table on the “Places” sheet and looked up longitude and latitude values for each of these places online.
Next, I built a table of each of the travels that he’s taken this year and named this table “Travels”. At its heart, this is really just list of each of the places that he’s visited including the date that he arrived. As you can see, he was born in Seattle, traveled to Coupeville then back to Seattle, then traveled to Berkeley and back to Seattle.
| Date | To Place |
| 12/1/2012 | Seattle, WA |
| 1/1/2013 | Coupeville WA |
| 1/8/2013 | Seattle, WA |
| 3/1/2013 | Berkeley, CA |
| 3/11/2013 | Seattle, WA |
Truth be told, I fudged the dates in this table a bit, both for privacy and to help the animation breathe a little.
I used VLOOKUP to find the latitude and longitude for the beginning and end points of each trip, and for each trip I picked an arbitrary number of “stops” along the way. These stops represent the number of orange dots that I draw on the map for each trip. From there I can do just a little bit of math to figure out how far apart each of those dots should be on the map. I also did similar math for “duration” which lets me map the dots over time to create a sense of movement.
Finally, I generated a big table called “Paths”. The heart of this table is really just a list of latitude, longitude, and time values. Each of these represent a single orange dot on the map at a particular moment in time. It turns out there are well over 500 dots in this tour, but each one is super easy to make! All I need is the date of the trip (a foreign key, for the database lovers out there) and a unique row for each of the numbered “stops” on each trip. From there, it’s simple addition to figure out where each dot should go.
Now I’m ready to map my data in Power Map! It’s fun and easy.
I just created a tour and added two layers to the default scene, one layer for my Places table and another layer for my Paths table.
I mapped my Places data by City, State, and Country and used its First Visit column (which contains the date on which he first arrived at each place) to make the blue city dots show up when he first arrives. I entered and used arbitrary Weight values in Excel to show his hometown of Seattle larger than the other places that he’s visited, and chose to use the Bubble chart type because I think it shows the concept of place relatively well.
I mapped Paths data by Latitude, Longitude, and Time, and set the Time value to “Data shows for an instant”. I added a Size dimension that just contains the value 1 for each data point. The reason I did this is that Power Map uses a nice grow/shrink animation on time-based data points when there’s a Size value, and I wanted to take advantage of this animation. Then I changed the aggregation function on my Size/Weight values to “No Aggregation”. This is so that Power Map doesn’t add overlapping values together for places that he’s been more than once since that would make certain paths look bigger than others. I shrunk the overall size of the bubbles through Layer Options.
I already have a pretty good map with just that many clicks. I could use a flat map showing North and South America at this point to see all of his trips over time, which is already pretty cool! But I really want to tell a story, and for that I need scenes.
Without changing my data at all, I went over the scene list and added scenes for each of the trips that I wanted to highlight. Then I zoomed in or out to position the virtual camera for each scene, and I set the Start Date and End Date for each scene (under Scene Options) to show just the trips that I want to highlight in that scene. Finally, I added a text box to each scene to tell the story and included data point annotations with pictures for a few scenes.
And that’s how I put this whole thing together. It was actually really fun! I just hope my son enjoys it when he’s older!
Blair Neumann Senior PM, Power BI