Cognitive Services in Power BI

Microsoft Cognitive services are collections of API and services that help developers to create more smart applications and reports.  By using Cognitive services, developers able to add intelligence features such as face recognition, emotion recognition, text analytics and so forth into their applications.

Cognitive Services Overview

There are different cognitive services available that you can use by receiving a web service URL and a key. To check out the available services you need to navigate to the cognitive services website[1].  There are five main categories for solving business problems as Vision, Knowledge, Language, Speech, and Search.

There are five different API for language business problem. Text Analytics is one of the main popular for language detection, identify the main keywords in text, topic extraction and checking how much text is positive or negative in context (sentiment analysis).

The other services in the Language category are about translating a text, identify the main intents, objects in conversations and texts. There is a possibility to combine these features. For instance, there is a possibility to integrate the Language Understanding service with the Speech service for instant Speech to Intent processing, and finally with Bot Application, to develop a sophisticated application.

In this post, I am going to see how we able to do text analytics for the aim of detecting the main keyword in customer feedback, how much the feedback is positive and detecting the language in Power BI using text analytics services. Text Analytics is one of the services in the Language category.

First, we need to set up the environment to use the cognitive services. To use cognitive services, you can use sign up for a free trial. The trial services are free for seven days and do not need any credit card. The other way is to use a free Azure account for a month, which is free and provides 280-dollar credit on Azure and all Data and customization will be saved. And The final approach is to use the Azure account.

To set up Cognitive services in Azure, you need to log into your Azure account and search for services you want.

After logging into Azure Portal, you need to create a Text analytics services. To create a Text Analytics service, you need to create a new service in Azure by clicking on the top left of the page. In the New page, the main service categories will be shown. AI and Machine Learning are one of the main services in Azure, click on it to see the available services. As you can see in the below picture, Computer vision, Face recognition, Text Analytics, Language Understanding, Translator Speech, Bing Search and Azure Search are one of the main services for AI and Machine learning.

Not all collected data is about numbers and structured data. To get a more holistic perspective about a customer, products and so forth, collecting and analyzing the text can enhance the company performance. Text Analytics is the process of converting unstructured text data into meaningful data to understand the customer needs and feedback [2]. The first step is to set up a Text Analytics service in Azure Portal.

There is different pricing tier for this service. The first one is a free tier that allows users to apply the Text Analytics service on 5K rows of data in a month.

After creating the Text Analytics service, we need to get the API URL and Key Access from the created Service.

After creating the service in Azure Portal, we need to call it inside Power BI desktop and apply it on the available data there.


Fabrikam company is a production company that sells products to their customer. The company received an email from the customer regarding shipment, tech support and other topics. The Business Intelligence manager want to do some analytics on the email they are receiving from their customers to better understand the main points of customer’s email and are they satisfied with service or not. You can download the dataset from [3].

The first step is to open Power BI desktop and load the dataset as a Text/CSV file into Power BI. Then instead of loading the data, click on the Edit option to navigate to the Power Query environment for data transformation.

First, we are going to combine the Subject and Comment column in Power Query using Merge Column in Add Column Tab.

After creating a new Column, we need to rename it to Customer Feedback.

Create a Text Analytics Function

There is a possibility to create a function in Power Query that apply Text Analytics to a specific column in Power Query. To create a function, we need to click on the white space in a query in the left side of the main page and choose Blank Query option.

Rename the Created Query to Sentiment Analytics. The Next Step is to convert the Blank Query to a function. To do that, we need to access the M Query behind it by clicking on the Home Tab Then Advanced Editor. There is an editor that we are going to write some M scripts and change the query to a Function.


The next Step is to add below code:

(text) => let

apikey      = “<API Key>”,

endpoint    = “https://<Location of You Azure>”,

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

As you can see in the above code, the first line is about the function input which is a Text. In line 2, the API key collected from Azure Service need to be paste here. In the line three, the required URL for connecting to API much be provided. As you can see at the end of the URL, the service that we are going to use from cognitive service has been shown. In this example, we are using sentiment analysis. From line 4 to 11 is the required codes for connecting to cognitive service and get the result in JSON format.  The last line is the result column that shows the sentiment score for each comment.

Just replace the above code with the previous one and push the Ok bottom. The Blank query will change to a function type and a page with a text box will show. You can test it by writing some sentence like the weather is so nice today and click on the Invoke bottom to see the result.

As you can see in the below picture, the result of sentiment analysis is 0.92.

The output of sentiment analysis is a number from 0 to 1. As much is close to 1 means the comment is positive and as close to zero means the feedback is negative.

In the final step, we able to apply the function to the Customer Feedback Colum. Click on the Fabrikam dataset, then click on the Add Column tab and then Invoke Custom Function. On a new page, choose an appropriate name for the new column, then choose the function in the dropdown, then in the last dropdown choose the column, you want to apply the function.

A new column will be shown at the end of the dataset overview that shows the numeric result for sentiment analysis. You can click on Home Tab and Close and Apply to see the result in Power BI Desktop.

The process of using the other services such as Language detection and Topic Extraction is the same. Just the URL endpoint will change slightly. For language detection, the endpoint URL will change as below

endpoint    = “https://<Location of You Azure>”,

and for Keyword extraction, the endpoint URL would be as

endpoint    = “https://<Location of You Azure>”


Moreover, the last line of code will change to show a different variable. Language detection it would be as below

language    = jsonresp[documents]{0}[detectedLanguages]{0}[name]

in  language

and for the keyword would be as below

keyphrases  = Text.Lower(Text.Combine(jsonresp[documents]{0}[keyPhrases], “, “))

in  keyphrases




Leila Etaati on LinkedinLeila Etaati on TwitterLeila Etaati on Youtube
Leila Etaati
Trainer, Consultant, Mentor
Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.

1 thought on “Cognitive Services in Power BI

Leave a Reply