In the second part of my blog series I will showcase another Azure Cognitive Services integration within Power BI.

Cognitive Services can be used by BI developers to add AI insights to their analysis. It consists of five major categories: Language, Vision, Speech, Search and Decision, each one containing various services.

The Text Analytics API offers natural language processing over raw text, and includes four main functions: sentiment analysis, key phrase extraction, language detection, and entity recognition. No training data is needed to use this API; The models are pre-trained by Microsoft natural language technologies which guarantees a high level of quality for a lot of use cases.

In this post, I will focus on key phrase extraction and language detection by integrating the Text Analytics API within Power BI.

I  will leverage “key phrase” and “language detection” to extract keywords from a text column and detect the language in which the comment was written.

For instructions on how to load data in Power BI & Prepare it for analysis  and How to setup a Cognitive services resource, please refer to Part 1 of my Integrating Cognitive Services Text Analytics API blog post.

For this demo I will use one of Microsoft’s fictional companies Fabricam data set which contains customer reviews of their service for fulfillment, technical support, praise, product info, shipping, suggestion and others.

Key phrase extraction

In this section I will demonstrate how to detect keywords in customer feedback by using Text Analytics services.

The custom function returns a list of strings highlighting the key points in the text column.

The script takes a text parameter, converts it into a JSON format, sends it to Cognitive Services by using API key and Endpoint and converts the returned JSON value back into a text field.

(text) => let
    apikey      = " Insert your API Key here ",
    endpoint    = " Insert your Endpoint here ",
    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

As a next step, we invoke the Key phrases function and add a column to our data table where we will store the key words for each comment. Here is how our Key Phrases column looks like.

Once we have extracted out Key phrases, we can apply them in various fields. They play an important role in text analysis, since they provide a concise representation of the article’s content. We can use the extracted phrases for document categorization, clustering, indexing, search, and summarization. Keywords are crucial for locating a text correctly in information retrieval systems, databases and for SEO as well.

Language Detection

The next Text Analytics service I will present to you is the Language detection one. It is useful, if we need to provide the text language as an input to other functions and for further analysis.

According to Microsoft’s official documentation there are two versions of the language detection function. It can either return an ISO language code – e.g. “en ” for English or a language name – e.g. “English” for English.

In my demo I have leveraged the first one and changed the assigned API key and endpoint with the ones we created in Part 1 of my Integrating Cognitive Services Text Analytics API blog post.

= (text) => let
    apikey      = " insert you API key here ",
    endpoint    = " insert your Endpoint here ",
    jsontext    = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),
    jsonbody    = "{ documents: [ { 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),
    language    = jsonresp[documents]{0}[detectedLanguages]{0}[iso6391Name]
in  language

After creating my Language function with the script above and adding a new “Invoke custom function” column, I receive the following “TextLanguage”column in my data table.

Sentiment analysis Visualization

Finally I am adding a sample Power BI visualization for the performed Text analysis.

I am using the Word Cloud visual to show the extracted key phrases. You can obtain this visual for free from the Power BI Marketplace. It is located on the Home page of Power BI Desktop application.

Another suitable and widely used visual for the grouped sentiment is the Pie chart together with a slicer.

Hope you enjoyed my post on Cognitive services!

In my next blog I will showcase how to perform Anomaly Detection by using Azure Cognitive Decision service within Power BI.

Stay tuned!