Today I presented at Power BI Global User Group, and demonstrated how to import Facebook group posts into Power BI, analyze the group engagement, and run sentiment analysis on the comments.
Here is the YouTube Recording:
Watch the recording to learn how to create the report below.
Subscribe to DataChant to get the Power BI Template file Facebook Group Analytics.pbit which was used to prepare the report above, and to get access to other great resources.
You can also download the template here. When you open the template, you will be prompted to provide an API key from Microsoft Cognitive Services (Find one here).
You may need to be a member of the Global Power BI user group for the refresh to work, or change the query to import data from a public group, or a group you are a member of.
If you encounter a Formula.Firewall error, please enable Fast Combine. Read here how to enable it.
Enjoy this session
Gil
hello. thanks for sharing this information. I tried for my CLOSED group and the table was generated empty; I guess the feature works only for OPEN groups?
It should work if you are the admin of the group
Hi Gil,
I might have missed it in your video, but is it possbile to get data about the countries/cities from your members in a Facebook Group?
Thank you.
Unfortunately, no. Insights can only run on pages.
thanks for your answer; I am admin, and the result is the one I mentioned, unfortunately.
thanks for your answer; I am admin, and the result is the one I mentioned, unfortunately.
Hi – Thank you for the explaining the procedure. I am however not able to complete the process as getting the following error post running the process of custom function:
An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from ‘https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment’ (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment
Please help.
Hi Vivek,
The error is usually triggered if your “id” column has duplicate values, or your “text” column has empty values.
I am having the same problem for a closed group in which I am admin. I am able to get back that some minimal information about the group such as the name, privacy level, connections, and the id. Has anyone had any luck with this yet? Or is this how it would be returned by the graph API even without Power BI?
Thank you for sharing. You can test the Graph API using Graph API Explorer https://developers.facebook.com/tools/explorer/
Hi Gil,
Your detailed explanation is highly appreciated. Many thanks.
I’m having the same problem as posted by Vivek R, even after removing duplicates id and blank texts:
‘’ query. DataSource.Error: Web.Contents failed to get contents from ‘https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment’ (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment”
Any idea as how to proceed?
Can this be related to microsoft cognitive upgraded version?
Thanks for your help
Hi Ofer,
Can you confirm that you used the columns “text” and “id”? Using different column names will return this error.
Please share the M expression, if you use the correct column names, and still have the error.
Hi Gil,
“id” and “text” are indeed my columns name. I still get this error….
the code for GetSentimentResults is a copy-paste of your code
The following is the code under the “Comment Sentiment” query which I’m trying to create (with the Invoke custom Function:
Thanks.
let
Source = Comments,
#”Removed Other Columns” = Table.SelectColumns(Source,{“comments.message”, “comments.id”}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Other Columns”,{{“comments.id”, “id”}, {“comments.message”, “text”}}),
#”Reordered Columns” = Table.ReorderColumns(#”Renamed Columns”,{“id”, “text”}),
#”Added Index” = Table.AddIndexColumn(#”Reordered Columns”, “Index”, 0, 1),
#”Integer-Divided Column” = Table.TransformColumns(#”Added Index”, {{“Index”, each Number.IntegerDivide(_, 2000), Int64.Type}}),
#”Grouped Rows” = Table.Group(#”Integer-Divided Column”, {“Index”}, {{“data”, each _, type table}}),
#”Invoked Custom Function” = Table.AddColumn(#”Grouped Rows”, “GetSentimentResults”, each GetSentimentResults([data]))
in
#”Invoked Custom Function”
Thank you for sharing. Can you edit the Integer Divide step from 2000 to 1000? This should fix the issue. The API doesn’t support sending 2000 messages in a single API call.
OK Gil, Many thanks. It’s working now.
Glad it works!!! Hope you’ll find it useful.
Hi Gil,
thank you for sharing this information !
I have one question regarding the topic – the MS sentiment analysis API is allowing to transmit only 5000 transactions per month for free and in case of reloading the file PowerBI will send the already evaluated records to the sentiment API.
In this case even if I pay for the Standard S0 or S1 plan (25,000/100,000 transactions per month) the suggested approach even very intuitive and easy to implement will be very inefficient and will quickly drain the transactions limit even for small facebook account especially if you will have to refresh frequently the PowerBI file in order to monitor the changes in real time.
Is there a way to avoid this limitation and transmit only the new comments to the API
Thank you for your help
Yes. You can currently achieve it in two ways:
1- Move the logic to Excel. Use VBA to implement incremental refresh. 2- Purchase the minimal Premium license and implement incremental refresh (easier to implement, but relevant when you have the budget and need the additional capabilities in Premium anyway).
Hi Gill
Some years down the track from your post now & pbi desktop (free version) no longer has the connector for Facebook that I can see. Is there any other way to get fb group page insights?
I am afraid that there isn’t. You need an access token in order to use the Web connector and it cannot be provided by Facebook for any app developer. You would need to be approved first.