Using Power BI for Facebook Location Tracking

Three years ago I wrote an article about how to map your Facebook check in in Excel using Power Query and Power View. Given that Power View has gone the way of the Dodo for all intents and purposes, I have been meaning to rewrite it using Power BI to do Facebook location tracking. During my recent Power BI tutorial at the European SharePoint Conference I was also informed that the Power Query behind it no longer works. Something had to be done!

The Facebook Graph API that Power Query uses changed significantly about a year ago, making location data much more difficult to discover. However, it is still possible to do, and below, I outline the process for working with Facebook data in Power BI. The below example is to map and analyze Facebook check ins, but the principles apply to all sorts of Facebook content.

Building the Query

To begin with, you’ll need to be running Power BI Desktop. You can do everything necessary with Desktop, but if you want to deploy to the service for automatic updates or for sharing, you’ll also need a Power BI account at app.powerbi.com.

To begin with, launch Power BI Desktop, select “Get Data” and scroll down to Facebook. Alternatively, you can select the “Online Services” node, and select Facebook from there. Click the Connect button, and if prompted, enter your credentials. Once that’s done you are prompted to enter an object id, and a connection.

There was a time when this could be used to gather data from any Facebook user, but with changes to the Facebook API, the Me object, or a Facebook page are the only objects that will work. The connection parameter allows you to select the data stream that will be returned. In this case, you will need the “Posts” connection. Once you click OK, you will be presented with a preview of the results. You will want to transform this data, so the next step is to select Edit.

At this point, you will notice that there are columns for message, story, time, Facebook ID, and object link, which contains further data about comments and likes. However, what you won’t find is any locational data, or even an indication as to the type of Facebook post. This will come as a surprise to people that used earlier versions of the Facebook connector. However, the data is still available.

If you look at the data source, you’ll notice that the “Me” and the “Posts” parameters entered above was used to form a URL. This URL queries the Facebook Graph, and the default result of that query is what you receive.

Checking the Facebook Graph documentation, you will find many different options for the Posts query. Most important is the ability to specify what data it returns, and indeed, there is a field named “place” that will return any location data for a post. Appending “?fields=place” to the query will cause the place field to be included to the result. However, if you do this, you will notice that most of the default fields are no longer included. You therefore need to include all fields wanted explicitly. Additional fields are specified by separating them with a comma. In our case, we will also return the fields for picture, and post URL. Our query is therefore:

Source = Facebook.Graph("https://graph.facebook.com/v2.8/Me/posts?fields=place,message,story,created_time,id,permalink_url,picture")

This query will return the posts and any available location data. If you want to only get the posts with location data, you can also add a filter specification to the query by appending “&with=location” to it. The full query therefore becomes:

Source = Facebook.Graph("https://graph.facebook.com/v2.8/Me/posts?fields=place,message,story,created_time,id,permalink_url,picture&with=location")

At this point, we have location data, but it’s in the form of a record, and you need to expand it. You can do so by clicking the expander icon at the top right of the column header. The only field that you want to retain is location, so first deselect the “Use original name…” checkbox and the “(Select All Columns)” checkbox, then select location, and click “OK”.

This returns location, also in the form of a column. Repeat the same steps to expand this column, but this time select all of them.

After selecting OK, your data has 4 new columns for the geographic information.

At this point, you need to flag the data types of your columns. Set the latitude and longitude to the decimal type, the created time column to Date/Time/Timezone and delete the object id column. If like and comment data is wanted, it can be added later in a separate query. Once the correct data types are set, select Close and Apply to load the data into the data model.

Building the Report

At this point you have all the data you need. If you want to use the permalink url and the picture columns to display links and pictures, you’ll need to flag the data category appropriately. You’ll also need to do this for your geographic columns. This is done by selecting the field in the fields selector, then opening the “Modeling” tab, and selecting the appropriate value for “Data Category”. Once done, the url field will render as a link or icon, the picture field will render as an image, and the geo fields will work on a map.

The precise values to set these fields to are as follows:

Once set, it is possible to build a report displaying check ins on a map, as a function of time, along with slicers and detail. By adding in comment and like data to the model as well, it is possible to create a report like the one below. To see how it was built, download this Power BI Desktop template file, open it in Power BI desktop, and enter your credentials. The report will populate with your check in data, and you can then see precisely how this report was built. If you just want to see all my Facebook check in data, you can slice and dice the report below. If everything is working properly, it should be updated daily. You can also see it fullscreen here – http://link.tygraph.com/FBJPW 

The important thing to note is that although the Power BI connector may not retrieve the data that you need for analysis by default, the full power of the Facebook graph is there for you to take advantage of. This is particularly valuable for doing analysis of Facebook pages.