Showing results for 
Search instead for 
Did you mean: 

Sentiment Analysis in Power BI

You may think that Sentiment Analysis is the domain of data scientists and machine learning experts, and that its incorporation to your reporting solutions involves extensive IT projects done by advanced developers. Well, today this is going to change.


Today I will show you how to gain Sentiment Analysis insights without the help of machine learning gurus or software ninjas. All you need to do is open your Power Bi Desktop and follow the steps below.


A short intro...

For the last six months I have been sharing Power BI scenarios, tips & tricks on my blog blog. One of the most popular scenarios described how to gain insights on your brands and your competitors through their presence on Facebook (Read more here). But I always had an itch to add Sentiment Analysis as new dimension for the insights. For example, with Sentiment Analysis we can better understand competitors' posts, or fans' comments and measure their emotional engagement or track down outliers of negativity in our campaigns.


So let's pretend we are the social media analysts of the US Presidential Candidates, and imagine we have a Power BI report like this one to analyze the reactions to Clinton's and Trump's Facebook posts.



Wouldn't it be cool, if we could also apply Sentiment Analysis on Clinton's and Trump's posts, and correlate the sentiment that arise from their messaging with the reactions they get?  Wouldn't it be awesome if we could drill down the posts by sentiment and high rate of specific Facebook reactions?


While you can already apply Sentiment Analysis in Excel using Azure Machine Learning Add-in (read more here), and there are already great posts on doing it the "Power Query" way (for example here), I think that there are many advantages to the technique I will share with you today (For example, you gain better integration in Power BI than with the Add-in; you can post more messages in a single API call; You are using the newest service rather than the soon-to-be deprecated Azure Marketplace),


Are you ready to begin?

To perform Sentiment Analysis on Clinton's and Trump's Facebook posts, we first need to pull down their posts. On my blog you can find several techniques to do so. Since our main focus today will be on the Sentiment Analysis part, let's start with an Excel workbook that I prepared in advance: Facebook Reactions Data Sample.xlsx (You can download it from the blog's attachments section).

The Facebook Reactions Data Sample.xlsx workbook contains 500 recent Facebook posts from each of the US Presidential candidates. We will import it to a Power BI Report, and apply Sentiment Analysis on it. Please download this workbook and save it to your computer. We will import it in a minute.


Are we going to see here some complex sentiment analysis algorithms?

Of course not. We don't need a mad scientist to help us this time. We will use out-of-the-box Sentiment Analysis API that is already offered for free by Microsoft Cognitive Services.

According to Microsoft, the Sentiment Analysis API "returns a numeric score between 0 and 1. Scores close to 1 indicate positive sentiment and scores close to 0 indicate negative sentiment. Sentiment score is generated using classification techniques. The input features of the classifier include n-grams, features generated from part-of-speech tags, and word embeddings. English, French, Spanish and Portuguese text are supported." (Read more here).


So before we start the tutorial itself, let's sign up for Microsoft Cognitive Services here.



After you click the Get started for free button above and follow the easy signup process for Text Analytics Preview, you will reach your account page (here), where you can obtain your Text Analytics API key. Click on the Copy link (as highlighted below) to obtain the key. You will need it soon.




Open Power BI Desktop, click on Get Data drop down menu and select Excel.




After you downloaded the attached Facebook Reactions Data Sample.xlsx, select that workbook in the Open dialog and click Open.




In the Navigator dialog, select the tables: Candidates, Posts, Reactions and ReactionTypes and click Edit.




Now in the Query Editor you can see a preview of the four tables and get a glimpse of the data we have at hand.


Our main focus today will be in the technique that allows us to upload the text data to the Sentiment Analysis API and load it to our report. We start by selecting the Posts query which contains the text for analysis. 

Right click on Posts in the left Queries pane, and click Reference.




Rename the query Posts (2) to Sentiment Results. To rename the query, right click on it and click Rename.




Note: In the next step we will select the columns in our data that contain the actual text for analysis and a unique ID for each text. The unique ID is required by Microsoft Cognitive Services API. The unique ID will help us to map the Sentiment scores in the response to the relevant text. In our data, we use the Facebook Post IDs as unique IDs. If you don't have a unique ID for the text in your own dataset, you can always use Add Column --> Add Index Column to obtain unique ID. 


In Home tab of Query Editor, click Choose Columns, unselect all columns, then select Post ID and Message and click OK.




Now, let's rename the columns. Post ID should be renamed to id. Message should be renamed to text. This step is critical. Don't miss it. The Sentiment Analysis API requires these names.




Note: If you need to perform analysis on text in French, Spanish or Portuguese, you can create another step here and define a custom column whose name is language and is value is "fr", "es" or "pt" (For French, Spanish or Portuguese). Since English is the default language in the API, we skip this step.


In the next step, we will remove rows with empty text messages. There is no point in sending such rows for Sentiment Analysis, and the service will return errors if we try to.


Click in the filter icon of the column text, and then click Remove Empty.




In the next step we will keep the top 1000 rows. We don't need to do it in our specific dataset that contains 1000 messages. We do it to prevent errors when you try these steps on your own data, since Microsoft Cognitive Services API allows only 1000 text messages in a single API request call.


But don't worry, if you have more than 1000 rows stay tuned to my next blog post here with a detailed walkthrough that will also show you how to perform multiple API calls. It was just too long for a single blog post.


In Home tab, click Keep Rows, and then click Keep Top Rows.




In the Keep Top Rows dialog, set 1000 as Number of rows and click OK.




Let's take a short stop, and let the query Sentiment Results have a break. We will return to it soon.

We will now create a new query that sends our data to the Sentiment Analysis service. 

In Home tab, click New Source drop down menu, and select Blank Query.




Rename the new query to GetSentimentResults, and In Home tab click Advanced Editor. Copy & paste the code below to the Advanced Editor main box, and then replace the part which is highlighted below in red with the API key that you obtained from Microsoft Cognitive Services (as mentioned above). 


(Source as table) as any =>
    JsonRecords = Text.FromBinary(Json.FromValue(Source)),
    JsonRequest = "{""documents"": " & JsonRecords & "}",

    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
                Headers = [#"Ocp-Apim-Subscription-Key"= "[Paste your API key here]",
                           #"Content-Type"="application/json", Accept="application/json"],
    JsonResponse = Json.Document(Response,1252)


After you place the API key, click Done in the Advanced Editor.


Note: Since you have inserted your API key in the code above, make sure with whom you share the Power BI Desktop file (.pbix). The API key will remain visible. If you don't wish to share the API key, you can use  Power BI template with the API Key as a parameter.




Going back to the query Sentiment Results, click on the little fx icon on the formula bar. If you don't see the formula bar you can enable it from the View tab.




 Change the formula from:


= #"Kept First Rows"





= GetSentimentResults(#"Kept First Rows")

Press Enter, and click Edit Credentials in the yellow business bar. In the Access Web Content dialog, keep the default Anonymous option, and click Connect.





You will now see a record of documents and errors, Click on the List object of documents.


Note: I will provide more details about error handling on a followup blog post here. For simplicity reasons, let;s ignore the few errors that the API returns (If you cannot wait for the followup blog, you can truncate the column text to 10240 characters to avoid those errors).




Click To Table in List Tools / Transform tab.




Click OK in the To Table dialog.




Expand the column Column1 by clicking on the little icon which is highlighted below.




Again, expand the column Column1. In the expand column pane, select score and id. Then uncheck Use original column name as prefix and click OK.




Right click on the header of column score and select Change Type, then select Decimal Number.




In Home tab, click Close & Apply.




That's it. With a single API call to Microsoft Cognitive Services, you got 1000 scores between 0 (Negative) to 1 (Positive) for the attached Facebook posts that were made by Clinton and Trump.


The next steps are the modeling and visualizations. There is nothing new there, so I will let you try it yourself before I publish a detailed walktrhough. Till I do, you can learn here how to create most of the visualizations below, including a cool Pulse Chart that triggers different events depending on your slicer manipulations.




Would you like to learn more? Follow my blog here where I will soon share with you how I created this dashboard.

You will also learn:


  • How to handle multiple API calls with 1000 messages on each call.
  • How to handle errors.
  • How to split the messages to sentences for better accuracy of the Sentiment Analysis alogirthm.
  • How to use DAX and a slicer to dynamically define the Negative, Neutral and Positive Sentiment thresholds.
  • How to corrolate Facebook reactions with Sentiment Analysis scores.


Thank you for reading,



Very informative and I ended up with some insightful data going through these steps. Thanks for taking the time to make it easy to follow even for newer PBI users like me.



After a very frustrating day, I was relieved to discover I'm not the only one suffering 'We couldn't authenticate with the credentials provided. Please Try again' issue.


What is even more upsetting the issue isn't new and appears unresolved. I do have to ask why?


Query code below:

QueryCode.png1_DataSource.png2 Data Source.pngPrivacyLevels.pngAuthenticateError.png


Is there a solution?


'Sentiment Analysis in Power BI' is a well-crafted article, unfortunately, let down by authentication issue.


Adoption of this technology or any other is dependent upon the technology delivering the expected outcome for users especially when users are learning its absolutely critical for both the provider and consumer. Even more, when issues are identified they are resolved in the same way the article has been written. Clearly defined and well-illustrated step by step guide.


Thank you.

@CABIRDUK I am sorry that it doesn't work for you. Can you confirm that you are using the same location (e.g. westus) on both the M code, and on your Text Analuytics resource on Azure? This may create the authentication failure. In addition, if you had it working in the past, and now you see the error, please ensure that your subscription is active, and that you didn't reach the API limit. These errors may occur when you reach the limit in your pricing tier (e.g. 5000 messages per month in the free tier).

Hope it helps,

Hello Gil


Location is westus. I'm in the UK if that has anything to do with it. 




M code

(Source as table) as any =>
    JsonRecords = Text.FromBinary(Json.FromValue(Source)),
    JsonRequest = "{""documents"": " & JsonRecords & "}",

JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
                Headers = [#"Ocp-Apim-Subscription-Key"= "[My API key here]",
                           #"Content-Type"="application/json", Accept="application/json"],
    JsonResponse = Json.Document(Response,1252)


Sadly I've never had it working.


New subscription account. Never used for anything else i.e. first use therefore limit of 5000 messages is zero. 


Hopefully the above helps to find a solution. Reading forum submissions I'm not the only one which suggests there is something wrong with the service.


Thank you for your help. Your assistance is very much appreicated.  

Hello Gil


Forgot to add I removed the [ ] not used in my M code just "My API key", however I did try with [ ] but that failed.  

Hello Gil


You were right.


Default source M code reads





For me (UK) it should read





Such a small oversight by me caused a great deal of frustration.


Thank you. Your assistance is appreciated.

I am getting error about authentication.

Please help me to sort out the issue.Authentication error.PNG




Done !!


I used 


for UK

Hi there,


Very helpful article, thank you, but I wanted to ask a question regarding privacy.


I have tried a similar exercise calling the Text Analytics API as a Custom Function from Power BI which works absolutely fine when privacy settings / edit permissions are set to Public for the excel data source, and Public for the API call.


However, for my purposes I want to try using this with organisational (potentially sensitive) data. I have therefore tried to specify Private for each source, or for one or the other, and each time get a message saying that it is not allowed.


Basically what I'm asking is - is the definition of Public actually misleading in the sense of that my data will be protected? Or is there no support for using sentiment analysis with Private data? I simply want a sentiment score against a series of comments, but I don't want it to be considered public.

For reference, the similar exercise I used before was following this article:


@Albro1989 setting the privacy as Public was inaccurate choice. You could use Organizational on both the Sentiment Analysis API as a data source and the source of text you need to analyze.

Assuming you are using Text Analytics in your own tenant, the data stays within the organization.

it will be safe to use it.


Note that this technique will require to turn off the privacy levels altogether because of over-protective limitation that can be ignored in this case.




I'm getting authorization failure error when I try to connect using the API key.


Please help.Screenshot (48).png

hi all ,

We are facing issue (404): Resource Not Found, Please help to solve below issue.:

"An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from '' (404): Resource Not Found






Any suggestions on how to use azure cog services key words and sentiment analysis with power bi when you have mulitple question focus group surveys? Ask is to evaluate within group per question sentiment and key theme, as well as compare groups per question sentiment and key themes. Have approx 60 groups that each have 10 open text answer questions, plus have average group ratings on 10 additional questions, plus some demographic data pertaining to the groups such as group category.