How To Use Text Analytics In Power BI

As a cloud architect, I recently had the opportunity to work on a project where I used Text Analytics in Microsoft Power BI. This Azure tutorial will discuss using Azure Text Analytics in Power BI with a realtime example.

How To Use Text Analytics In Power BI

Here, we will have a Product and some Review Comments from different users, which include Key Phrases and the sentiment of the text (positive or negative). This is the report we have to generate, which will help the vendor understand the quality of the product from the user’s perspective so that the vendor can improve the product quality if needed.

So, if we start developing this requirement using custom code, achieving this functionality will be difficult. It will take a lot of time and resources. But we can quickly achieve this requirement using the Azure Text Analytics service with the help of Power BI. Before starting the development, we should know What Azure text analytics is and the Prerequisites needed to start the actual functionality.

Before starting the actual development, we should know the prerequisites.

Prerequisites

• You must have an Azure account or a valid Azure Subscription. If you haven’t had an Azure account yet, create a Free Azure Account now.
• Power BI Desktop.

Create and configure Azure Text Analytics

You need to follow the below steps to Create and configure Azure Text Analytics.

1. Log in to the Azure Portal (https://portal.azure.com/)

Once you log into the Azure Portal, click the + Create a Resource button from the left panel.

2. Search for the Text Analytics as shown below.

For the same option, once you log in to the Azure Portal, search for Text Analytics in the global search and click on the search result Text Analytics under the Marketplace.

3. On the Create window, provide the details below to create the Azure Text Analytics service.

• Name: Provide a unique name for the Azure Text Analytics service.
• Subscription: Choose the correct subscription that you want to use to create the Azure Text Analytics service.
• Location: Choose a location for the Azure Text Analytics service.
• Pricing Tier: Choose a pricing tier based on your requirements. You can choose the Free F0 for the demo or use a maximum of 5K transactions per 30 days. To learn more about the pricing structure, click the view complete pricing details link.
• Resource Group: Choose an existing Resource Group, or you can click on the Create new link to create a new Resource Group.

Finally, click the Create button to create the Azure Text Analytics service.

You can see that the deployment is completed successfully.

4. Click the Go to Resource button to navigate to the Azure Text Analytics service you created.

5. Now Our Azure Text Analytics service is ready, The next step is to copy the Key1 value of the Azure Text Analytics service, and we will keep it in Notepad as we are going to use this in the Power BI in the next section. To copy the key1 value, click on the keys and Endpoint link from the left navigation, and then you can see the Key1 and Key2 values. Click the copy button as highlighted to copy the Key1 value and keep it in a notepad.

You can also click the Show keys button to see the value of Key1 and Key2, or click the Regenerate Key1 and Regenerate Key2 buttons to regenerate a new key for these.

Now, our Azure Text Analytics service is complete. The next is to start with the Power BI changes. This is how we can create and configure Azure Text Analytics in the Azure Portal.

Configure The PowerBI Report To Read Textual Data

Well, here we will Configure The PowerBI Report To Read Textual Data. We will use PowerBI Desktop for this purpose.

I have captured the Product Review comments in an Excel sheet for implementation. It contains the column values below.

• Date
• Users
• ProductComments

Load Data Into Power BI

Now is the time to load the Excel data to the Power BI Desktop. Follow the steps below to load the Excel data into the Power BI.

1. Open the Power BI Desktop as shown below.

2. Once you open the Power BI Desktop, select the Home ribbon, open the Get Data drop-down menu, and select Excel—option to import the data from the Excel sheet.

3. Select your Excel sheet and click the Load button on the navigator window.

After loading the data from the Excel sheet, if you want to view the data, you can click on the Data button from the left navigation as highlighted below.

Create Custom Functions

As the next step, we must create custom functions that integrate the Power BI with Azure Text Analytics. You might be thinking, How exactly will it work ?. The custom functions we will create will receive the text to be processed as a parameter, then it will convert that data to and from the required JSON format and make the HTTP request to the Azure Text Analytics API. Then, the custom function will parse the Azure Text Analytics API response and return the response.

As per our requirement, which we have discussed before, we will have to get the Key Phrases and the Sentiment Score. So, as per the requirement, we will create two custom functions here. Those are as follows.

• Key Phrases: This will return a list of key phrases extracted from the ProductComments.
• Sentiment Score: Will return a score between 0 and 1.

To Create the custom function Key Phrases, follow the below steps

Click on the Home tab in the ribbon. Click on the Get Data dropdown menu and then select Blank Query. You can see a new query with the name quey1 in the Queries list. Double-click on query 1 and rename it KeyPhrases.

Now, on the same window, click on the Advanced Editor from the Ribbon on the Home tab. This will open the Advanced Editor window. You will see some default code there. Delete that default code and paste the code below there.

(text) => let

    apikey      = "dd7687fd21xxxxx609795870xxx4f6f84",

    endpoint    = "https://eastus.api.cognitive.microsoft.com/text/analytics/v2.1/keyPhrases",

    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

Note: You can also use the API version of 3.0 here. This means the EndPoint URL can be used as https://eastus.api.cognitive.microsoft.com/text/analytics/v3.0/keyPhrases

So, the complete code as per V 3.0 will be as shown below. You can use the code below as well

(text) => let

    apikey      = "dd7687fd21xxxxx609795870xxx4f6f84",

    endpoint    = "https://eastus.api.cognitive.microsoft.com/text/analytics/v3.0/keyPhrases",

    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

In the same way, we will add the other custom function, SentimentScore, delete the default code, add the code below in the Advanced Editor window, and then click on the Done button.

(text) => let

    apikey      = "dd7687fd21xxxxx0979587xxx4f6f84",

    endpoint    = "https://eastus.api.cognitive.microsoft.com/text/analytics/v2.1/sentiment",

    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

Note: You can also use the API version of 3.0 here. This means the EndPoint URL can be used as https://eastus.api.cognitive.microsoft.com/text/analytics/v3.0/sentiment

Now, you can see the two custom functions created under Queries below.

Invoke the Custom Functions

Now, it’s time to invoke the custom functions we just created. Navigate to “Query1” or click on Query1 as shown above. Select the Add Column tab from the ribbon, then click the Invoke Custom Function option.

Provide the New column value as keyphrases in the Invoke Custom Function window. Choose the Custom Function KeyPhrases we have created before as the Function query option, then choose the text (Optional) option as ProductComments. Finally, click on the OK button.

Now, a banner will appear asking you to specify how to connect to the Edit Credentials button. Click on the Edit Credentials button.

On the Access Web content window, Make sure to choose the Anonymous option, then the “select which level to apply these settings to” option, and then click on the Connect button.

Now, you will see a banner asking for information about data privacy with the Continue button. Click on the Continue button.

Select Public for all the options on the Privacy levels window and click the Save button.

Now, we need to invoke the other function, SentimentScore, in the same way. So, follow the same process above to invoke the KeyPhrases custom function.

Choose the New column name as SentimentScore and Function Query as the custom function that you have created for the Sentiment score, i.e., SentimentScore, and choose the text (Optional) option as the ProductComments and then click on the Ok button as highlighted below.

Now we are done with the changes. Navigate to the Home Tab on the Ribbon and then click on Close & Apply. It will take a few minutes to process, and the pop-up below will be shown to you.

After a few minutes, once it has completed processing, you can see it will show you the data view with the two additional columns with respective values based on the ProductComments.

FAQs

What is Azure text analytics?

Azure text analytics service is one part of Azure cognitive services that helps you perform different operations efficiently, like key phrase extraction, sentiment analysis, language detection, named entity recognition, etc. You will have many requirements in these areas, and Azure text analytics helps you achieve these functionalities quickly.

Azure text analytics supports different languages, including Chinese, Danish, Dutch, English, French, German, Greek, Italian, Korean, Polish, Japanese, Russian, Swedish, Spanish, and Turkish.

You may also like following the articles below

• How To Implement Azure Face API Using Visual Studio 2019
• Azure Cognitive Services Face API
• Cannot Open Server Requested By The Login Azure SQL

Wrapping Up

This article discusses how to use Azure text analytics in Power BI. I hope you have enjoyed this article.

Rajkishore

I am Rajkishore, and I am a Microsoft Certified IT Consultant. I have over 14 years of experience in Microsoft Azure and AWS, with good experience in Azure Functions, Storage, Virtual Machines, Logic Apps, PowerShell Commands, CLI Commands, Machine Learning, AI, Azure Cognitive Services, DevOps, etc. Not only that, I do have good real-time experience in designing and developing cloud-native data integrations on Azure or AWS, etc. I hope you will learn from these practical Azure tutorials. Read more.