Using Microsoft Azure Text Analytics and Power BI Integration
Photo by Mason Jones on Unsplash
The Sentiment Analysis
Microsoft Azure Cognitive services and Power BI Integration
The total amount of data created, captured, copied, and consumed in the world is forecasted to increase rapidly, reaching 74 zettabytes in 2021 (Source: Statista). Imagine how much of this 74 zettabytes data will be unstructured and untamed, leaving a huge void 💣in how data scientists around the world would analyze, model and consume this mammoth amount of data. For example, product reviews on ecommerce websites like Amazon or free speech on social media giants like Facebook and Twitter.
One of the key challenges with such with unstructured data is how to gauge public opinion, conduct nuanced market research, monitor brand and product reputation, and understand customer experiences. In layman terms, how to understand and classify emotions or for data scientists how to perform a sentiment analysis. A Sentiment Analysis is the process of determining whether a piece of writing is positive, negative or neutral.
What will we Discuss?
In this story, we will perform sentiment analysis on a sample set of data and use :
- Microsoft Azure Text Analytics (For performing sentiment analysis)
- Power BI (For integration and Visualization)
Here is the link for the sample data that we will use: Sample Data.
Resources Required
- Microsoft Azure Subscription (Free Trial or Paid)
- Microsoft Power BI Desktop (Pro License)
Are you ready?? Here we go 🏄
Step 1: Azure Text Analytics
Login to the Azure Portal: https://portal.azure.com/#home, search for “ Text Analytics ”

Create a Text Analytics service by selecting subscription, creating a resource group (just a container to bind the resources), location and pricing tier. A free web container allows 5,000 transactions free per month. After clicking “Review + Create” , Azure may take a couple of minutes to create the resource.

Once the text analytics resource has been created, navigate to “Keys and Endpoint” and copy the keys and endpoint details probably in a notepad.
⚠️ Please keep a note that keys and endpoint should not be disclosed to unauthorized people as they may impact your azure consumption cost. Regenerate keys if you have accidently disclosed the same.
Now, you are done with the Azure Portal portion now and can navigate to Power BI.
Step 2: Power BI Integration
Open a new instance of Power BI desktop>> Import Data from Excel>>Browse the sample data file >> Bingo! You got your dataset imported in Power BI inching closer to the world of visualization.
Use the APIKey and Endpoint link from Step 1 and replace the placeholders in below M query script, which basically helps to perform API calls to Azure. Another important parameter to know here is the language: ““en”” which can be tweaked to include more than 20 languages (Hindi, Chinese, German, French to name a few).
= (text) => let | |
apikey = "<<Replace your APIKey here>>", | |
endpoint = "<<Replace your endpoint link here>>", | |
jsontext = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))), | |
jsonbody = "{ documents: [ { language: ""en"", id: ""0"", text: " & jsontext & " } ] }", | |
bytesbody = Text.ToBinary(jsonbody), | |
headers = [#"Ocp-Apim-Subscription-Key" = apikey], | |
bytesresp = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]), | |
jsonresp = Json.Document(bytesresp), | |
sentiment = jsonresp[documents]{0}[confidenceScores] | |
in sentiment |
Now go ahead and select Get data>>Blank query (this will navigate to Power Query editor)>> Paste the M query code (containing replaced API Key and Endpoint link)


It will be a 🔦 good idea to rename the “Query1” to something meaningful like “Sentiment API”. Go ahead and edit it either by right clicking on left hand side pane or directly editing in Name field on right hand side pane.
Now, in the power query editor itself navigate to the dataset and under Add Column section>>select “Invoke Custom Function”>>Add column name=Sentiment, select function query from drop down and pass text as the column with feedback “Answer”>>Press OK.

This will invoke the Azure API and create a new column, Expand the column “Sentiment” and change the data types for 3 newly created columns (positive, neutral, negative) to “Whole Number”. While you can keep them in decimal format, I would recommend to change them to whole numbers as it helps to visualize them better. Now you can hit “Save & Close” and let the Power BI do its magic! 🎉

Before you finally enter visualizations, it will be useful to create a new calculated column which can be used to provide slicers for easy filtering. You can collate everything into one column using below DAX formula.
Overall Sentiment = IF(Responses[Sentiment.positive]=1,"Positive",IF(Responses[Sentiment.neutral]=1,"Neutral",IF(Responses[Sentiment.negative]=1,"Negative","NA")))
Step 3: Power BI Visualization
Now you are ready to visualize 📊 the information. I would recommend to use the following approach:
- A horizontal slicer to filter sentiments by Positive, Negative and Neutral.
- A bar chart to represent sentiment trend by week, month or year.
- Some emojis to represent the feeling and emotions of the audience.
- A table providing all the raw data fields to correlate and “Export to Excel”.
- A disclaimer to specify the limitations of the AI model.
*Being a predictive AI model, this may not be 100% accurate representation of the sentiment but it does helps with actionable insights and quicker decision making providing indicative sentiments. #responsibleAI #transparency.
Last but not the least, you can also add conditional formatting to ensure color schema matches with sentiments.
Here is an example of sample Power BI template that you can use as a starting point. Sentiment Analytics Azure + Power BI Integration DEMO.pbix

Output
By slicing and dicing the filter you can see how effective Azure text analytics resource in performing sentiment analysis. For example, feedbacks like “good session” and “wonderful event” were correctly classified as positive 😃 and similarly feedbacks like “slower connections” and “lot of problems” were correctly classified as negative 😞.


Conclusion
We learned 📘 how to use Microsoft Azure Text Analytics for sentiment analysis and how to integrate the analysis in Microsoft Power BI to develop visualizations.
You could use other datasets and customize the code to see what suits your use case best!
Came across a different approach for sentiment analysis? Please drop it in the comments !
References
[3] Data source: prepared manually by the Author
Follow me on Linkedin, Medium, GitHub for more stuff like this
Categories: Data Science