How To Use Azure Text Analytics In Power BI

In this Azure tutorial, we will discuss How To Use Azure Text Analytics In Power BI. Along with this, we will also discuss a few other topics like What is Azure text analytics?, Create and configure Azure Text Analytics, Azure cognitive services power BI.

How To Use Azure Text Analytics In Power BI? Below are the steps to use Azure Text Analytics in Power BI.

• Create and configure Azure Text Analytics
• Configure the PowerBI Report To Read Textual Data

How To Use Azure Text Analytics In Power BI

We will discuss how to use Azure Text Analytics in Power BI or Azure Text Analytics and Power BI here. This is an exciting topic.

Here, we will have a Product and some Review Comments from the 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 know about 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 is 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.

What is Azure text analytics?

Azure text analytics service is one part of Azure cognitive services that helps you perform different operations easily, like key phrase extraction, sentiment analysis, language detection, named entity recognition, etc. You will get a lot of requirements in these areas. Azure text analytics helps a lot to 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.

Oh, now we have a little idea of what Azure text analytics is. Before starting the actual development, we should know the prerequisites.

Prerequisites

• You must have an Azure account or a valid Azure Subscription. Suppose you haven’t had an Azure account till now. 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

Login to the Azure Portal (https://portal.azure.com/)

Once you log in to the Azure Portal, click on the + Create a Resource button from the left side panel.

Search for the Text Analytics as shown below

Or, 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.

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. You can click the view complete pricing details link to learn more about the pricing structure.
• 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.

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

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 part is done. 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 as part of the implementation. It contains the below column values.

• Date
• Users
• ProductComments

Load Data Into Power BI

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

Open the Power BI Desktop as shown below

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.

Navigate to your Excel sheet, select it, 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 will have to create custom functions that integrate the Power BI with Azure Text Analytics. You might be thinking, How exactly will it work ?. The custom functions that we are going to create will receive the text to be processed as a parameter, and then it will convert that data to and from the required JSON format and will 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 that we have already 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 below

• 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. Now, a new query with the name quey1, you can see in the Queries list. Double-click on the query1 and rename it to 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 the V 3.0 will be like below. You can use the below code 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 that are 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.

On the Invoke Custom Function window, Provide the New column value as keyphrases. 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, it will display a banner asking you to specify how to connect with the Edit Credentials button. Click on the Edit Credentials button.

On the Access Web content window, Make sure to choose the Anonymous option and then “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, in the same way, we need to invoke the other function i.e., SentimentScore. 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 will show you the below pop-up

Now, 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.

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
• CS1061 C# ‘HttpRequest’ does not contain a definition for ‘Content’, and no accessible extension method ‘Content’ accepting a first argument of type ‘HttpRequest’ could be found

Wrapping Up

Well, in this article, we have discussed How To Use Azure Text Analytics In Power BI, What is Azure text analytics?, how to Create and configure Azure Text Analytics, and Azure cognitive services Power BI. I hope you have enjoyed this article.

Rajkishore

I am Rajkishore, and I have over 14 years of experience in Microsoft Azure and AWS, with good experience in Azure Functions, Storage, Virtual Machine, 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.