none
Facebook Query: How to get total_count from Facebook API to display in Power Query RRS feed

  • Question

  • I have had a lot of trouble working with the facebook query.  Basically I would like to access the total likes, shares, and comments per post for a facebook page. I understand the aggregation function to get the total number of likes' and comments' ids. But this seems to always return an error if there are more than about 2 days worth of posts (about 10 posts).  Is there anything I can do to get this data in one pull?

    The code I was typing into https://developers.facebook.com/tools/explorer was:

    (PAGE-ID)/post?fields=name,shares,likes.limit(0).summary(1),comments.limit(0).summary(1)

    any help would be more than appreciated!!!!

    thank you

    Wednesday, July 15, 2015 3:10 AM

Answers

  • Couple different ways to do this.  One, expand "likes" and then Group By "message" and choose Count as your new column in the dialog that appears.  Basically by expanding "likes", you have as many rows for a post as there are likes so that when you Group By and choose Count you end up with just the message and the aggregate Count.  You could do this again for shares and perhaps a third time where you don't expand anything and just retrieve the "message" and "time" and remove all of the other columns.

    Then, in Power Pivot, in Diagram View you wire the three tables together based upon "message". Now you have a model that has only what you want.

    That being said, you could also do the same thing by importing all the raw data and creating a few measures to do counts of likes and shares and such.

    Wednesday, July 15, 2015 1:31 PM
  • Are you getting an error in Power Query or from the Facebook API page? If the later, you probably should really ask this question on a Facebook developer forum. If the former, what is the error you are receiving?

    This Power Query query should get you everything you need, assuming it is for yourself.

    let
        Source = Facebook.Graph("https://graph.facebook.com/v2.2/Me/posts"),
        #"Expanded likes" = Table.ExpandTableColumn(Source, "likes", {"id", "object_link", "name"}, {"likes.id", "likes.object_link", "likes.name"}),
        #"Expanded comments" = Table.ExpandTableColumn(#"Expanded likes", "comments", {"can_remove", "created_time", "from", "id", "like_count", "message", "object_link", "user_likes"}, {"comments.can_remove", "comments.created_time", "comments.from", "comments.id", "comments.like_count", "comments.message", "comments.object_link", "comments.user_likes"}),
        #"Expanded shares" = Table.ExpandRecordColumn(#"Expanded comments", "shares", {"count"}, {"shares.count"})
    in
        #"Expanded shares"

    Wednesday, July 15, 2015 4:12 AM
  • By using group by, this should cut down the amount of data retrieved tremendously, basically only 1 line per post so I would think this would be a way more efficient way to go and not cause so many data retrieval errors or bumping up against limits.
    Thursday, July 16, 2015 12:43 PM
  • Sounds like a limitation in the Facebook API that it is limiting likes to 25 and since you have more likes than that for some posts it is throwing an error.  Not sure why that would surface when trying to do a COUNT aggregation versus when just expanding them into rows but I'm not that familiar with the Facebook API. I know that I was able to do it for all of my posts, but apparently not many people like my posts. :(
    Thursday, July 16, 2015 5:02 PM

All replies

  • Are you getting an error in Power Query or from the Facebook API page? If the later, you probably should really ask this question on a Facebook developer forum. If the former, what is the error you are receiving?

    This Power Query query should get you everything you need, assuming it is for yourself.

    let
        Source = Facebook.Graph("https://graph.facebook.com/v2.2/Me/posts"),
        #"Expanded likes" = Table.ExpandTableColumn(Source, "likes", {"id", "object_link", "name"}, {"likes.id", "likes.object_link", "likes.name"}),
        #"Expanded comments" = Table.ExpandTableColumn(#"Expanded likes", "comments", {"can_remove", "created_time", "from", "id", "like_count", "message", "object_link", "user_likes"}, {"comments.can_remove", "comments.created_time", "comments.from", "comments.id", "comments.like_count", "comments.message", "comments.object_link", "comments.user_likes"}),
        #"Expanded shares" = Table.ExpandRecordColumn(#"Expanded comments", "shares", {"count"}, {"shares.count"})
    in
        #"Expanded shares"

    Wednesday, July 15, 2015 4:12 AM
  • Thank you Seth.

    The problem will still remain as I am really not looking for any info about the post other than Expanded shares, total_count of likes, and total_count of comments. Perhaps I should get help from a facebook developer.  

    But the reason I posted on here is that when using the facebook explorer tool it can clearly show the  

    "likes": {
            "data": [
            ]
    ,
            "summary": {
              "total_count": 358,
              "can_like": true,
              "has_liked": false
            }

          }
    ,

    All I want power query to show me other than name of post, time, expanded shares, is this "likes" total_count.

    Does this make sense?

    It's almost as if I want it to be a seperate table column and that way it wont be so labour intensive to retrieve the data that I am not really interested in? 

    • Edited by myklaws Wednesday, July 15, 2015 4:26 AM
    Wednesday, July 15, 2015 4:24 AM
  • Couple different ways to do this.  One, expand "likes" and then Group By "message" and choose Count as your new column in the dialog that appears.  Basically by expanding "likes", you have as many rows for a post as there are likes so that when you Group By and choose Count you end up with just the message and the aggregate Count.  You could do this again for shares and perhaps a third time where you don't expand anything and just retrieve the "message" and "time" and remove all of the other columns.

    Then, in Power Pivot, in Diagram View you wire the three tables together based upon "message". Now you have a model that has only what you want.

    That being said, you could also do the same thing by importing all the raw data and creating a few measures to do counts of likes and shares and such.

    Wednesday, July 15, 2015 1:31 PM
  • Hi Seth, 

    Thank you so much for your assistance. I will try this ASAP, if I could ask though, where I believe part of my problem rests is in the fact that this particular facebook page (lets use buzzfeed as an example) has a lot of likes and comments, and this is I fear what may be causing the problem. I was using the aggregate funciton but it would return an error, will the group by be a better way to achieve this without rendering an error? If this is the case that there are large amounts of shares, comments, and likes are there any tricks I could do to get a large query of data? Or will it have to be broken down into a day or two at a time?

    mike

    Thursday, July 16, 2015 3:28 AM
  • By using group by, this should cut down the amount of data retrieved tremendously, basically only 1 line per post so I would think this would be a way more efficient way to go and not cause so many data retrieval errors or bumping up against limits.
    Thursday, July 16, 2015 12:43 PM
  • Alright, now I apologize again for being a newb.  I've expanded the column, deleted unnecessary columns and hit 'save and load.'  Should I have done 'group by' in the power query editor or is it better to do it once the data is loaded into an excel spreadsheet? Currently its been 'saving and loading' the data for about an hour lol

    Thursday, July 16, 2015 1:36 PM
  • Ok data loaded successfully, for about a months worth of posts.. I have the likes expanded and into a worksheet.  Problem being they are not grouped.  So I went back into query editor and tried to group by count.  Again, error and an indication that likes.limit(25) so I did that and again, error. It's almost as if the aggregation/grouping is a real problem for the query and it is this that is causing me the greatest headache.

    Mike

    Thursday, July 16, 2015 3:41 PM
  • Sounds like a limitation in the Facebook API that it is limiting likes to 25 and since you have more likes than that for some posts it is throwing an error.  Not sure why that would surface when trying to do a COUNT aggregation versus when just expanding them into rows but I'm not that familiar with the Facebook API. I know that I was able to do it for all of my posts, but apparently not many people like my posts. :(
    Thursday, July 16, 2015 5:02 PM