Partner The Knowledge Base
Share this content
Colourful earth globe

Cutting Big Data down to size – part five: Animated map

13th Oct 2017
Partner The Knowledge Base
Share this content

Simon Hurst continues his Cutting Big Data down to size content series with a step-by-step guide on how to amalgamate and use data from different web pages to animate a map.


In the first part of this series we discussed possible definitions of Big Data and considered whether some of the technology and techniques involved could be of use to businesses of all sizes.

In part two we used Microsoft Power BI Desktop to combine some internal accounting data with over 1.6m rows of data downloaded from the Ofcom website and plotted the resulting values on a Power BI Map visualisation.

Part three concentrated on Power BI data visualisations and how they interact. The first part of this two-part special used the IAAF world championship medal table for the 2015 championships in Beijing as an example.

We saw how Power Query/Get & Transform could help with the acquisition of data from a web page and the subsequent processing into the data structure needed to visualise the data using Excel 2016 3D Map.

Finished part one

This time, we are going to adapt our existing query to allow us to amalgamate the data from different web pages and use a data and time field to animate our map.

Multiple web pages

As we saw last time, each of the IAAF world championship medal tables has a different URL. We have added a sheet to our workbook to contain a table that holds the element of the URL that varies from championship to championship.

We have added a column to hold an approximate date for each championship that we can use to animate our 3D Map. We have named our Table tblChamps:


Our existing query takes a URL and uses it to extract the medal table for that webpage and restructure it. We need to run that query for each of the rows in the above table. The way we do that is similar to the way we would use a function in Excel. If we needed to perform a calculation on each row of an Excel Table, we might use an Excel function that used one or more of the Table columns as its arguments. For example, we could extract the last four digits of the Championships URL element using =RIGHT([@Championships],4).

For our query, we will read in our championships Excel Table and add a new column that contains a function. Our function will be a little more complex than RIGHT(), both in the way that it works and in the result that it delivers. The function will encapsulate all the operations that our original query performed and, unlike our RIGHT() function, will return multiple rows and columns of data.

We'll start by basing a query on our Championships Table. We select any cell in the Table and choose From Table (From Table/Range in later updates) from the Power Query/Get & Transform group of the Data Ribbon tab.

Parameters and Functions

The next few steps are probably a bit more complicated than the previous aspects of Power Query that we have covered. Our Beijing query uses a fixed string of text as the source URL. We need to be able to change this to retrieve data from each of the championships pages in turn. To do this, we need to change our fixed text into something that can accept a changing parameter.

We start by choosing New Parameter from the Manage Parameters dropdown in the Parameters group of the Query Editor Home Ribbon tab. Here, we have created a new parameter called Champs to hold some text and set the current value to the specific text for the Beijing page:

New parameter

We now need to edit the Source step in our Beijing query to use our Champs parameter. Currently the Web.Contents function includes the entire URL as text:

= Web.Page(Web.Contents(""))

We need to change this so that the variable part is replaced by our parameter:

= Web.Page(Web.Contents("" & Champs & "/medaltable"))

As our Parameter is currently set to this value anyway, we should see no difference if we click on the final step to see our query result. We could go back to the Manage Parameters dropdown and use Edit Parameters to type in the text for a different championship. We could use Refresh Preview to see the different data returned by using the new parameter value.

We are now well on the way to achieving our objective. We just need to replace our manual parameter with the contents of the Championships column of our Table.

Turning a query into a function

We right-click on our Beijing query in the Queries pane of the Query Editor and choose Create Function:

Create function

We have given our Function the name AllChamps.

We now return to the query based on our Table of championships and years. From the Add Column Ribbon tab, General group we can choose Invoke Custom Function. This lets us choose our newly created AllChamps function and decide which column of our query to use as the function argument:


We have given our Function the name AllChamps.

We now return to the query based on our Table of championships and years. From the Add Column Ribbon tab, General group we can choose Invoke Custom Function. This lets us choose our newly created AllChamps function and decide which column of our query to use as the function argument:


We now have a new column in our query that contains a Table for each row. We can use the Expand icon at the right of our new column's heading to expand our Table to show all of our columns without using the 'original column name as prefix':

All champs

We can Close & Load our query to our workbook where we can use it in the same way as last time as the basis for a 3D Map, but this time we can drag our Year field to the Time area to animate our graphic over time:

Animated map

The Time area has three options for how the animation should deal with the values. We can either just show the data for each time period briefly before the bubble disappears completely; we can accumulate the date over time which would, in our example, show the total number of medals won across all the included championships, or we can show the data value until a later period replaces it.

Once a field has been added to the Time area a play control appears at the bottom of our map allowing us to play through our periods and to change the Time Options that control the range of dates used and the play speed for example.

As well as animating the charts superimposed on the map, a Tour can consist of multiple scenes and each scene can use a variety of effects to animate the overall chart such as rotating the 3D globe. Once you are happy with the tour that you have created, you can save it as an MP4 video file.


Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.