PASS Pro Now Available

Welcome to the next evolution of PASS. Unlock exclusive training, discounts, and networking opportunities designed to accelerate your data career. Learn More >

The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Azure Cognitive Services via Power BI

Text Mining and Sentiment Analysis can provide interesting insights, when used to analyze free form text like social media posts, customer reviews, feedback comments and survey responses. Key phrases extracted from the text are useful to identify trends and popular topic/themes. Sentiment scores provide a way to perform quantitative analysis on this text data. Combining these techniques, using visually engaging dashboards will help unlock the value of your text data.

In this two-article series about Text Mining and Sentiment Analysis, we will start with exploring Azure Cognitive Services -Text Analytics APIs, learn how to sign up and get an access key. We will be loading team health Survey Data from a fictional company, into Power BI and learn how to call the Text Analytics API, to extract Key Phrases and generate Sentiment Scores

In the second article of this series, we will do look into various qualitative and quantitative techniques to analyze this data and create effective visualizations in Power BI.

(These articles assume basic familiarity with Power BI and Azure)

Article 1

Have you looked at social media posts, customer reviews/feedback, survey responses to open ended questions, or pretty much any free form text and wondered about analyzing that data? Maybe you have tried doing this in R or Python and wondered if you are using the right lexicon or library for the task? Have you wondered about how to visualize the outcome of such analysis, create Dashboards and share it with relevant people in your company? Let’s use Power BI and Azure Cognitive services, to perform these tasks – no advance programming expertise or Guru level of technical knowledge needed!

The Text Analytics API is a part of Azure Cognitive Services, a collection of machine learning and AI algorithms in the cloud. It provides advanced natural language processing over raw text, and includes four main functions: sentiment analysis, key phrase extraction, language detection, and entity recognition. This article focuses on Key phrase extraction and Sentiment Analysis.

Key Phrase Extraction API returns a list of important words/phrases, for each document and is used to identify the main points/themes/topics in your text. For example, for the input text "The food was delicious and there were wonderful staff", the API returns the main talking points: "food" and "wonderful staff".

Sentiment Analysis API returns a sentiment score between 0 and 1 for each document, where 1 is the most positive and 0 is the most negative score. It is useful to find out what customers think of your brand or topic by analyzing raw text for clues about positive or negative sentiment. The analysis models are pretrained using an extensive body of text and natural language technologies from Microsoft. For selected languages, the API can analyze and score any raw text that you provide, directly returning results to the calling application.

You have several options for using the Text Analytics APIs. 

  • Evaluate Azure Cognitive services for free, as a Guest with a 7 Day Trial
  • Use Azure Cognitive services for non-production work loads, through your free Azure Account
  • Use it for Production workloads, through your existing enterprise Azure account

You can always give it a try from your browser, using this link -

Step 1 – Set up Azure Cognitive Services resource

Assuming you have an Azure subscription/Account, let’s set up Cognitive Services resource using the Azure Portal.

  1. Sign in to the Azure portal, and click +Create a resource
  2. Use the search bar and enter “text analytics”
  3. Select “Text Analytics” from the search results and hit the Create button
  4. On the Create Screen;
    1. Enter a name for your service
    2. Choose a Subscription from the drop-down list
    3. Choose a location
    4. Choose a pricing tier that best fits your needs (please review the pricing details)
    5. Select an existing resource group or create a new one
    6. Then hit the Create button

Figure 1. Create Text Analytics Service

Your deployment will begin and may take a few seconds to complete. Once its ready, click to open it and make a note of the Endpoint as well as the Access Keys. You will need them to Integrate Power BI with the Text Analytics Cognitive Service.

Figure 2. Make a note of the Service Endpoint

Figure 3. Make a note of the Access Keys

The Text Analytics APIs are now ready for using in Power BI. We will be using a Team Health Survey from a fictional company, to walk through the rest of this demo. The raw data in Excel spreadsheet has 4 fields;

  • Period (Year & Quarter number)
  • Manager (has the manager name)
  • Team (has the team name)
  • Response (has the free form text responses from the Survey, to the question – How do you feel about your team’s health in this recent quarter)

Here’s a sample of the first few rows;

Figure 4. Raw data sample

Step 2 – Loading Data into Power BI

Lets’ load this data into Power BI. After opening Power BI Desktop, select the Home ribbon, open the Get Data drop-down menu and select Excel. The Open dialog will appear. Navigate to your Excel file and click the Load Button

Figure 5. Load Data window

To see the loaded data, click the Data View button on the left edge of the Power BI workspace. A table containing this data will open.

Figure 6. See the loaded Data

Step 3 – Creating Custom Functions

In this scenario, no other data preparation/transformation is needed, so we can proceed to the next step of creating the custom functions that will integrate Power BI and Text Analytics. The function receives the text to be processed as a parameter. It converts data to and from the required JSON format and makes the HTTP request to the Text Analytics API. The function then parses the response from the API and returns a response. Power BI Desktop custom functions are written in the Power Query M formula language, "M" for short. We will be creating 2 functions;

  1. Key Phrases – which will return a string that contains a comma-separated list of the extracted key phrases.
  2. Sentiment Score – which will return a numeric score ranging from 0 to 1

In Power BI Desktop Navigate to the Query Editor window. In the Home ribbon, in the New Query group, open the New Source drop-down menu and select Blank Query.

A new query, initially named Query1, appears in the Queries list. Double-click this entry and rename it KeyPhrases.

Now, in the Home ribbon, in the Query group, click Advanced Editor to open the Advanced Editor window. Delete the code that's already in that window and paste in the following code.

// Returns key phrases from the text in a comma-separated list

(text) => let

    apikey      = "YOUR_API_KEY_HERE",

    endpoint    = "YOUR_ENDPOINT_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),

    keyphrases  = Text.Lower(Text.Combine(jsonresp[documents]{0}[keyPhrases], ", "))

in  keyphrases


After saving the “KeyPhrases” function, we will follow the same steps and create a “SentimentScore” function, pasting the following code in the Advanced Editor Window

// Returns the sentiment score of the text, from 0.0 (least favorable) to 1.0 (most favorable)

(text) => let

    apikey      = "YOUR_API_KEY_HERE",

    endpoint    = " YOUR_ENDPOINT_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}[score]

in  sentiment


You should now see both custom functions under Queries on the left side;

Figure 7. Custom Functions in Query Editor Window

Step 4 – Invoking the newly created Custom Functions

Now we can use these custom functions to extract key Phrases and generate Sentiment Score for each of the text responses and store them as new columns in the Table.

In Power BI Desktop Query Editor window, switch to the “TeamHealth_RawText” Query and select the Add Column ribbon, then click Invoke Custom Function.  The Invoke Custom Function dialog appears. The Invoke Custom Function dialog appears. In New column name, enter keyphrases. In Function query, select the custom function you created, KeyPhrases. A new field appears in the dialog, text (optional). This is asking us which column to provide as input values for the text parameter of the API. Select Responses from the drop-down menu and Click OK.

Figure 8. Invoke the KeyPhrases Custom Function

After you close the Invoke Custom Function dialog, a banner may appear asking you to specify how to connect to the Key Phrases API. Click Edit Credentials, make sure Anonymous is selected in the dialog, then click Connect.

Figure 9. Authentication Dialog box

Next, a banner may appear asking you to provide information about your data sources' privacy. Click Continue and choose Public for each of the data sources in the dialog and Save.

Figure 10. Privacy levels Dialog Box

Now Repeat these Invoke Custom function steps for “SentimentScore”

Figure 11. Invoke Custom Function for SentimentScore

After closing all the dialog boxes, Power BI may need a few minutes to process before you can see the 2 new fields added and populated

This would be a good time to Transform the SentimentScore field to Decimal Data type and round it to 2 decimal places

Figure 12. Data preview of adding 2 new fields and rounding SentimentScore field

Then go to Home ribbon and click Close & Apply

Power BI will need a few minutes to process these queries, during which time you should see the screen below

Figure 13. Power BI Processing

Once all the processing is complete, your Data View will now show the table has been updated with 2 new fields, that are loaded with KeyPhrases and SentimentScore for each Response text.

Figure 14. Power PI Data View with KeyPhrases and SentimentScore fields loaded

The data table now updated with KeyPhrases and Sentiment scores is ready for use do further analysis and create impactful visualizations in Power BI.

At this time, you can log into the Azure Portal and review the utilization metrics of your Text Analytics Service. You should see a spike in the chart for Total Calls, around the time Power BI was processing your data, buy invoking the Text Analytics API.

Figure 15. Azure Portal – Utilization Metrics for your Text Analytics Service

In the next article of this two-part series, we will look at qualitative and quantitative analysis techniques for this data in Power BI. We will create a word cloud, and several statistical charts to help with analyzing this data, extract business value and use Power BI visualizations to narrate a meaningful story about this data.

Sanil Mhatre
About the author

Sanil Mhatre is a Senior Data Engineer, currently focused on delivering analytical insights for a technology solutions and services company in St. Louis. He has a master's degree in information systems and enjoys working with various data processing technologies, analytics tools, and visualization platforms.

Sanil is a budding data scientist, an active member of PASS, and a frequent speaker at several local and regional technical events. In his spare time, he volunteers with numerous STEM mentorship programs, and blogs to keep up with developments in the fields of data science, Machine Learning, and AI.

Please login or register to post comments.

Theme picker

Back to Top