Month: December 2014

For me, 2014 was a particularly busy year. I was fortunate enough to have been invited to speak at a number of international conferences, and took a few wonderful personal trips as well. When travelling, I have gotten into the habit of doing Facebook check ins as I go. It occurred to me after describing my year to a friend, that since Power Query supports Facebook, I should be able to visualize my travel year easily enough.It wasn’t only easy, it was also very fast.

Acquiring the data

Getting data from your Facebook feed is really quite easy. Once you have Power Query installed, open a new Excel workbook, select the Power Query tab. Then from the “Other Data Sources” button, select Facebook.

Unless they’ve already been saved, you’ll be prompted to enter Facebook credentials. Once you’ve done so, you’ll be prompted for a Facebook object, and a Facebook connection (or feed).

There is a wealth of data that can be retrieved, but in our case, we’re interested in check-ins, which are expressed in Facebook as posts. Therefore we select “me” (the currently logged in user) and “Posts” and click OK. The Power Query editor loads with all of our Facebook posts. It should be noted that Power Query is using Facebook Graph to perform the query, as can be seen in the function bar:

That import function can be edited, so any valid Facebook graph expression can be used to query data, not just those automatically generated by the Power Query UI.

Unfortunately, “Posts” contains a lot more data than just check ins, so we need to filter it down. We do that be applying a few transformations. All check ins are status posts, so we start by filtering the type column, selecting “status” as the filter value.

Not all statuses are check ins. However, all statuses with a value for location are. Where is location? It’s in a related record in the “place” column. The place column values are related records, so first, we need to expand the place column. To do so, click the small “expand” icon next to the column title.

Next, select the columns that you want to expand. In this case, all we need is location, so that’s all we need to expand. When OK is selected, the location data is expanded, and the original place column is replaced with a new place.location column. However, the values for location are themselves records, so they also need to be expanded. We then follow the same procedure that we did for “place” to expand them.

Once the columns are expanded, it is fairly obvious that only some entries have values for longitude and/or latitude. These are our check ins. We need to filter out all of the other records, and we can do this by filtering latitude or longitude with using the same approach that we used on the “type field earlier. This time, we deselect the null value.

We are left with nothing but check ins as records.

Now it’s time to clean up the data – we don’t want to load any more data than we need to perform the analysis, so we delete any unnecessary columns. Also, the expanded column names are cumbersome, so we rename those as well. Finally, the two date fields don’t get auto-detected as dates, so we need to change their data types to Date/Time. IN our case, the completed query appears as below.

Visualizing the data

We are now ready to visualize the data, so we select close and load. In our case, Power Query has been set to load only into the model, but your settings may vary. Since we’ll be using Power View, we need it in the data model. After the data has loaded, we select the Insert tab, and select Power View from the ribbon.

The first thing that we want to see is a map of our logins, so we select Country from our Power View fields which loads a list of the countries. Then we select Map from the ribbon, which loads the map. Next, we resize the map to fit our requirements. Finally, we drag the id field to the size field (visually showing number of check ins per location) and then we drag the city field under country in the locations field. At this point we can double click on a data point to drill down to a city.

We want to restrict the data to 2014, so we drag the created_time field into the filters column, select the advanced filters option, and select greater than Jan 1 2014 and less than Jan 1 2015.

Finally, we want to show a list of all of the locations, so we click just below the map visualization and select created_time, Country, state, and city from the fields list.

Normally, I’d finish the post up with a screenshot of the finished post, but this post on the Power BI blog yesterday inspired me to try embedding the actual product. Play around and have a look!

 

1 Comment