Get data from Facebook with Excel 2013, Power Pivot and Power Query

By | January 2, 2014

First of all happy new year!  Lets make it a great Power BI year, one of my resolves is to do more blogging this year.

My wife was talking to me about the need for her to get some information from a Facebook page she is admin for. A project that is getting more and more traction across the us and the globe is the “buy nothing project” (http://buynothingproject.org/) . This project is a hyperlocal Facebook group that enables members to post anything you’d like to give away, lend, or share among neighbors in the same city. Instead of buying it in a store you can get it from a neighbor who doesn’t need to have that specific item any more.

In the project admins group a thread got started on how to get more information on these local group. So I thought lets see if I can get this information from Facebook using Power Query and into Excel. Some information that might be useful is to be able to see if the members of the local group actually live in the same city as the group or what is the percentage of male / female posts or what is the most active hour etc etc.

And I managed to get it working Smile so lets take a look at how this works.

In order for you to be able to do this you need to install: Excel 2010 with PowerPivot for Excel 2010 and Power Query or Excel 2013 (most versions) with Power Query.

Lets get started, open Excel 2013, open the Power Query tab and select import from other sources, From Facebook:

image

Now you need to log in with your Facebook credentials that will be stored on your computer, The window that opens next asks for a “object” in the Facebook graph.

image

Facebook graph is the primary way for external apps to talk with Facebook. This allows you to get all the information you can think off from Facebook and Power Query can handle all this information. For more information see: https://developers.facebook.com/docs/graph-api/

In order to figure out what the ID of the object is what you want to get information on is open that page in the browser and copy the number at the end. This is your Object ID. I created a test group where I added some colleagues and friends.

image

Now each object in Facebook has many connections, you can find all the connections that are available from the API documentation mentioned above or you can select the connection you want to use from the dropdown in Power Query. In our case I want to see the member list:

image

I press OK and this opened the Power Query Editor which loaded the data from my Facebook group which I named “Members” (I got rid f the name column for privacy reasons):

image

I wanted to get some additional information so I click on the header of the object_link column, this opens a small list of items that get returned by Facebook:

image

One thing that I found out is that some items are not shown because Facebook wont return items that are not available for all members. In my case I want to get the location of the member if available. Many members don’t have their location so you want be able to select this by default. So what I did is filter the list down to only me by clicking on the header above the name field and selecting “Kasper de Jonge”. When I now click the object link we get a much larger list where I select “gender” and “location”:

image

This adds two columns but the location is still not usable as Facebook returns data in a hierarchical shape and not a neat row that we expect in Excel. Luckily Power Query knows how to handle this.

image

I clicked on the icons in the header again and select name:

image

This gives me the members with the information that I need for my group. I select Load to Data Model and press “Apply & Close”:

image

This loads the data into Excel and the Power Pivot model. I open the query again by clicking on the Workbook button in the Power Query tab. This opens a pane with all the queries in my workbook, I hove over the one that I want and click “Edit Query”:

image

Now that I have all the columns I remove the filter by clicking on the X mark next to the filtered row step:

image

This now loads in all the rows into the Power Pivot Model, I open the Power Pivot window by clicking on the PowerPivot tab and select “Manage”:

image

Now the next thing that I want to get is the posts done to this group. I again go to import from Facebook and this time I select “Feed” as connection. This will download all the posts done in this group. Again I make a selection of the fields that I want, similar as before and import the data into the Power Pivot model:

image

This gives us all kinds of interesting details on the posts made. One additional action that I took when importing the data was add some aggregations to the list. For example comments is not a cell in this table but actually a reference to another table, instead of importing all the comments made on this post I want to see the nr of comments. I can click on the arrows icons

image

This will give me a UI that allows me to choose what I want to do. Do I either want to Expand the data or Aggregate the rows. In this case I choose Aggregate by the number of ID’s of the comments:

image

Now the last thing I want to do is create a relationship between the members table and the feeds table so I can combine the information from both together. I open the Power Pivot window again and switch to the diagram view:

image

Here I drag the memberID on top of the ID column of members, thus creating a relationship. Some data I already know I want to report on is the number of members and number of feeds. I open the Data view again and add two calculated fields in the calculation area:

image

Nr of posts:=COUNTROWS(Feeds)

and

Nr of members:=COUNTROWS(‘Members’)

Now I am ready to do some analysis. I go back to Excel and create some simple reports:

image

or

image

From here there are tons of things we can do and create all kinds of reports from the data.

  • Kasper, thanks for posting this! Glad to hear you will be blogging more. Also, sent you a FB request, which of course you are free to ignore 🙂

  • Kasper – thanks for spelling this out for us, I thought the process was going to be much more involved with in-cell web queries or pre-processing or VB: I have been a long-time (but old-school) Excel user and was completely unaware of Power Query. This will be an excellent tool to help track & analyze Buy Nothing Project development & characteristics.

    Regards, Paul

    • Kasper de Jonge

      great to hear 🙂 let me know if you have any questions, getting the data in is part 1. After that you will be able to start doing analysis using the Excel data model (or Power Pivot) which is very powerful but can be complex. Let me know if you have any questions.

  • Hi Kasper. Great tool. Just wondering if it can be made to work in Open Office?

    • Kasper de Jonge

      Nope, this is all build my Microsoft and uses some Excel pieces like the Data Model that are not in any other tool.

  • Hi Kasper,

    Can this system not be used in Excel 2007?

    • Kasper de Jonge

      Hi Andrew, this can only be used on Excel 2010 or higher.

  • Hi Kasper, Thank you for taking the time to crate this ‘how to’! I’ve been using the query for some time now and recently it stopped working. I’m on Excel 2010 on Windows 7. I get an error when trying to load data “Expression.Error: The evaluation terminated unexpectedly. Details: at System.RuntimeMethodHandle._InvokeConstructor(Object[]…”

    I’ve done some poking around online trying to figure out the problem with no success. Would you have any insights?

    Thanks in advance for your time!

    Crescent
    Buy Nothing NE Seattle

    • Kasper de Jonge

      Hi,

      Could you use the “send a frown” button when you get this error message. It looks like something went wrong here. I contacted the team and this is not something they have seen before, sending a frown will send some information to the team so they can look into it.

      Thanks,
      Kasper

  • Thanks for this – very helpful. Do you know if there is a way to import a members join date to a facebook group?

    • Kasper de Jonge

      I’m sorry I don’t, if its available through the facebook API you should be able to use it.

  • Thank you for this great blog, well written.

  • Hi Kasper, Thanks for this
    when i ask for post it brings me only the post for one year back , can i ask for post 2 or 3 years back ?
    And also at the buttom of the facebook grath list you can choose “custom” ..what can we do with that ?
    thanks !!

  • Vipul Mistry

    How does this handle large fan bases? I have a FB page of 500K and 1M fans. I want to figure out which fans comment, how often they comment.

  • mapu123

    I can’t see the location column. I see locale, but location is not available. How about about?

  • mapu123

    I can’t see the location column. I see locale, but location is not available. How about about?

  • mapu123

    How about if we type the account id, I can’t find location either. thanks

  • mapu123

    How about if we type the account id, I can’t find location either. thanks