August 15, 2019

How to connect Power BI with the Cognitive Services and Text Analytics API

How to connect Power BI with the Cognitive Services and Text Analytics API?

You can visualize and transform your data with Microsoft Power BI Desktop.

In order to analyze natural language processing we will make use of the Text Analytics service which is part of Microsoft Azure Cognitive Services. Having an unstructured text, you can analyze sentiments by extracting the most prominent key phrases.

1. First step is to load the data

In order to load the data, go to Get data drop-down menu and select load CSV/ Text file.

Open the CSV file in the folder where you downloaded it to import the CSV file into Power BI.

Next you click on load since Power BI Desktop has detected the header rows, delimiter and column types.

2. Now you have to prepare the data.

In order to make use of the Key Phrases API of the Text Analytics service, transformation of the data may be required.

The CSV file has a comment and a subject column. We will merge both these columns in order to extract key phrases from both columns.

Therefore, you have to click on edit queries where you select both columns in the table by holding the Control key button.

In the text columns of the transform ribbon you will see the merge columns ribbon. Choose the following separator as below.

3. Get an understanding for the API.

4. Now you can build up a custom function.

What happens when creating the custom function is that data is converted from the JSON format and a HTTP request is made to the Key Phrases API which delivers a string with a comma-separated list of the extracted key phrases. Custom functions are written in Power Query M language that is based on F#.

You have to be in the Power Query Editor window where you select Blank Query. Query1 will show up which you have to name KeyPhrases. Go to the Advanced Editor where you insert the following code as stated below:

Insert your API key with your Text Analytics access key. This key can be found by logging in to the Azure portal, selecting your Text Analytics subscription. Do not forget the quotation marks before and after the key.

5. Make use of the custom function.

Now click on Invoke Custom Function to extract the key phrases from the customer comments and store it in a new column which you call keyphrases and store it in the custom function that you created.

What happens here is that Power BI calls your custom function once for each row in the table. Queries are sent to the Key Phrases API and a new column with the stored results show up.

Authentication and privacy settings are asked before this can take place. Choose Anonymous due to the fact that the Text Analytics service authenticates you using your access key, so credentials for the HTTP request itself are not needed. Click on Connect.

Next choose Public and save.

Now you can create a word cloud instead of using the full text of every comment. As a result we will see the most important words from the customer comments and not only the most common words.