Share via

Accessing my 'Big Data' with PowerQuery and PowerMap

  • Article

One of the great things about Office 2013 is that it includes something called 'Self-service Business Intelligence' which allows mere data-crunching-mortals like you or I to play with data through a tool that we understand i.e. Excel.  After watching a demo of these two tools being used,  I downloaded PowerQuery for Excel and PowerMap Preview for Excel and installed them on my laptop. 

PowerQuery is actually set of tools but its primary purpose is to give you access to data sources including the usual suspects like SQL and Access.  Perhaps more importantly,  rather than downloading the information into Excel straight away,  you can filter, remove and merge data before you download it because the dataset you are looking at could be considerable in size.  There are a number of data sources available but two stood out for me in particular.

One is the ability to pull data from an 'Online Search' which queries a number of online tables and databases, including 1.5 million tables on Wikipedia.  This in itself is very powerful as the average user can now pull data freely available on the internet into Excel to manipulate.  Merge that with your own database of information and you have can start to combine your store sales information with publically available population information for counties or towns,  making decisions based up real data not guess work.  

The other dataset that caught my eye was perhaps a more considerable dataset with thousands if not millions of data points,  Facebook.  I connected to my Facebook data via the Facebook Graph API and started to sift through the data structure.  In the end I decided I was going to filter on 'check-ins' and pull the time,  latitude and longitude data.  At the time of writing this blog it was 730 records but you can imagine how much time and bandwidth was saved by using PowerQuery to download only the data I need from my 'Big Data' store that is my Facebook account.  I now have my data locally in Excel to play with.

PowerMap was the next tool I wanted to use to visualise the data.  It's one thing to see a list of long/lat combinations but it doesn't really mean much to the average person.  After splitting the timestamp field into date and time columns (using PowerQuery again) I selected all of the data and loaded it into PowerMap.  A few dropdown boxes later and I have a heat map of my check-ins in Reading, Berkshire over the space of approximately three years. 

PowerQuery and PowerMap really do bring self service business intelligence to the masses.  I have spent as long learning about these tools as you have reading this blog so get stuck in and have a try yourself!

Check out the video of my check-in heat map for Reading.  For those of you that know Reading well .... don't judge me on the location of most of my posts !