Mapping UK Highways Agency traffic data in PowerBI Desktop

May 10, 2017

I downloaded the May 2017 version of PowerBI desktop to load in some of the traffic data that I dropped into Qlik Sense and Tableau in a previous post.

c 02 map

The finished article first

So - back to getting started. This is my first time using the software, and it'a pleasant experience right up until I try to load an .xls file.

c 00 missing driver

A missing driver? Already?

I have to admit, I'm a little surprised that the driver required to connect to a .xls file isn't included with the download. I would expect .xls to be one of the most common files loaded into the desktop version of PowerBI. It turns out the access database engine is used to connect to earlier versions of excel files, and another download is needed - but enough of that!

Loading the data

A nice interface and good progress updates are a welcome touch, so aside from the driver issue I loaded the 2012-03 dataset (~800MB CSV) quickly. Then, a simple drag and drop joins the lat/lon coordinates to my data.

c 01 linked table

Loading the data is simple, only the AverageSpeed field was transformed

As per my Qlik and Tableau post, I convert the speed from kilometres to miles. There's a load of other changes I could make to trim long decimals and tidy up the dates but that's for another day.

Visualising in a map (bubbles)

To load into the map and have it look a little similar to my other examples was challenging, as PowerBI doesn't appear to provide as much flexibility out of the box for colour or bubble size. The bubble size is still gigantic even when set to 0%, and you need to set diverging gradients quite carefully to ensure the middle colour doesn't look out of place.

c 02 map

It's a busy map

A couple of nice additions popped out though, including the in-built binning feature. This is available in Qlik GeoAnalytics, but not in the core map product - I had a quick play and although it doesn't make sense to apply it on this dataset, it's a welcome feature. I'll need to check Tableau at a later point.

c 03 bins

Binning is a welcome feature

The other element of interest is the "Quick Insights" feature. It pulled some insights that I agree with (even if they are common sense on this flat dataset). I'm keen to try it with something a bit more exciting and see what it turns out.

c 04 quick insights

The Quick Insights feature provided some valid feedback

My favourite here is the top right - that average journey time correlates with the distance travelled (well, yeah). But, again, I'm keen to see this on a more interesting dataset.

And...the app

Another embedded app to play with. Like the Tableau embeds I'm pleased that this one also links to the full screen content!

Profile picture

From Dave, who writes to learn things. Thoughts and views are his own.

© 2024, withdave.