How To Use Azure Text Analytics In Power BI

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.

Create and configure Azure Text Analytics

Search for the Text Analytics as shown below

How to Create and configure Azure Text Analytics

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.

How to configure Azure Text Analytics

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.

How to create Azure Text Analytics

You can see that the deployment is completed successfully.

configure Azure Text Analytics in Azure Portal

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

Use Azure Text Analytics In Power BI

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.

How to configure Azure Text Analytics in Azure Portal

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
Configure The PowerBI Report To Read Textual Data

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

How to Configure The PowerBI Report To Read Textual Data

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.

Create The PowerBI Report To Read Textual Data

Navigate to your Excel sheet, select it, and click the Load button on the navigator window.

tutorial power bi text analytics azure

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.

How To configure Azure Text Analytics In Power BI

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.

How To configure Text Analytics In Power BI
(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.

Azure Text Analytics and Power BI
(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

How To Use Text Analytics In Power BI

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.

Azure Cognitive Services via Power BI

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.

Power BI and Azure text Analytics API

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.

Power BI and text Analytics API

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.

Use Text Analytics In Power BI

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

How To consume Azure Text Analytics In Power BI

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.

Integrate Power BI with the Text Analytics Cognitive Service

You may also like following the articles below

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.

1 thought on “How To Use Azure Text Analytics In Power BI”

Comments are closed.