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

Well, here we will discuss How To Use Azure Text Analytics In Power BI or Azure Text Analytics and Power BI. This is really an interesting topic.

• Resource Not Found Error Face API
• How To Convert m4a File To Text Using Azure Cognitive Services

Here, What we will exactly do is, We have a Product and some Review Comments from the different users which include Key Phrases, the sentiment of the text (positive or negative). This is the Report we have to generate and that will really help the vendor to know, how about the quality of the Product from the User’s Perspective. So that the vender can improve the Product quality if needed.

So If we will start developing this requirement using custom code, it’s really difficult to achieve this functionality and will take so much time and resources, But we can easily achieve this requirement using the Azure Text Analytics service with the help of the Power BI. Before starting the actual development we should know What is Azure text analytics? 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 to 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 quickly achieve these functionalities.

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

Ohk, now we have a little bit of idea on the What is Azure text analytics?. Before starting the actual development, we should know the Prerequisites needed for the actual development.

Prerequisites

• You must have an Azure Account or a valid Azure Subscription. If you don’t have 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 logged in to the Azure Portal, from the left side panel, click on the + Create a Resource button.

Search for the Text Analytics as show below

Or, For the same option, Once you logged in to the Azure Portal, Search for the Text Analytics on the global search and click on the search result Text Analytics under the Marketplace.

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

• Name: Provide a unique name for the Azure Text Analytics service.
• Subscription: Choose a 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 requirement. You can choose the Free F0 for the demo purpose or you can use a maximum of 5K transactions per 30 days. You can click on the view full pricing details link to know 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 on the Create button to create the Azure Text Analytics service.

You can able to see, The deployment is completed successfully.

Click on the Go to resource button to navigate to the Azure Text Analytics service that you have created just now.

Now Our Azure Text Analytics service is ready, The next step is we will 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 able to see the Key1 and Key2 values. Click on the copy button as highlighted to copy the Key1 value and keep it in a notepad.

You can also click on the Show keys button to see the value of the Key1 and Key2. You can click on the Regenerate Key1 and Regenerate key2 buttons to regenerated a new key for the Key1 and Key2.

Now Our Azure Text Analytics service part is done. The next is we will 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.

As part of the implementation, I have captured the Product Review comments in an Excel sheet. It contains the below column values.

• Date
• Users
• ProductComments

Load Data Into Power BI

Now 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 opened 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 and select the Sheet and then click on 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

Now as the next step, we will have to create the custom functions that will actually integrate the Power BI with the Azure Text Analytics. You might be thinking How exactly it will 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 response from the Azure Text Analytics API and returns 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 here as per the requirement we will create two custom functions here. Those are as below

• Key Phrases: Will return list of key phrases those are extracted from the ProductComments.
• Sentiment Score: Will return a score between 0 to 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 able to see in the Queries list. Double click on the query1 and rename it to KeyPhrases.

Now on the same window, From the Ribbon on the Home tab, click on the Advanced Editor and now it will open the Advanced Editor window. You can able to see some default code there, Delete those default code and paste the below code 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. Meaning 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

Now in the same way, we will add the other custom function i.e SentimentScore and delete the default code and add the below code 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. Meaning the EndPoint URL can be used as https://eastus.api.cognitive.microsoft.com/text/analytics/v3.0/sentiment

Now you can able to see the two custom functions that are created under Queries as below

Invoke the Custom Functions

Now, the time to invoke the Custom Functions that we have created just now. Navigate to the “Query1” or click on Query1 as shown above. Select the Add Column tab from the ribbon, then click on the Invoke Custom Function option.

On the Invoke Custom Function window, Provide the New column value as keyphrases and Choose the Custom Function KeyPhrases that 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 show you a banner asking you 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 information is required about data privacy with the Continue button. Click on the Continue button.

On the Privacy levels window, select Public for all the options and then click on the Save button.

Now, in the same way, we need to invoke the other function i.e SentimentScore. So follow the same process that we followed 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 the Close & Apply. It will take few minutes for processing and will show you the below pop up

Now after a few minutes, once it will complete processing, you can able to see it will show you the data view with the two additional columns with respective values based on the ProductComments.

So, this is How To Use Azure Text Analytics In Power BI using the above steps as described.

You may also like following the below articles

• How To Implement Azure Face API Using Visual Studio 2019
• Azure Cognitive Services Face API
• How To Create Azure Cognitive Services PowerShell
• 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?, Create and configure Azure Text Analytics, Azure cognitive services power bi. Hope you have enjoyed this article.