Power BI

Sentiment Analysis with Power BI

I was recently asked by a customer to help them do some Text analysis of course evaluation data. They wanted to perform sentiment analysis and key phrase extraction. My first thought was to develop an Experiment using Azure Machine Learning (Azure ML). However, after doing a little research I stumbled upon a Text Analytics API that accomplishes exactly what the customer asked. It is built using Azure ML and can be found in the Microsoft Azure Marketplace (https://datamarket.azure.com/dataset/amla/text-analytics). There are several subscription levels, I opted for the 10,000 free transactions a month.

 

After registering I clicked on the Interactive Demo link located in the bottom right corner of the Text Analytics subscription page. This page provided me we a great place to interactively test out the API. You can also find code samples for building console applications using C#. Now, typically I would have launched Visual Studio and put on my developer hat. However, after reading the documentation on the API, I realized that API is a suite of web services.

 

Hmmmm, a web service. Now what do I have in my tool belt that can extract data from a web service, Power BI Desktop. So I opened Power BI Desktop and selected Get Data from the ribbon. Then I selected the OData Feed from the Other data connections section. Next I used the following url:

https://api.datamarket.azure.com/data.ashx/amla/text-analytics/v1/GetSentiment?Text=hello+world

as the URL for the OData feed.

 

image

 

Then I clicked OK.  After clicking OK, you should be prompted to supply credentials for the OData feed.  Since the web service is hosted in the Azure Marketplace you will need to supply your Account Key.  Select Marketplace key in the left navigation section and enter your account key in the available textbox.

 

image

 

If you need to obtain your key, click the Get your Marketplace Account Key hyperlink and you will be directed to a webpage that contains the key.  You may need to log in.  Either way, copy the key and paste it in the Account Key text box.  Click the button labeled Connect.

 

Now, this is where the fun actually begins.  The Query Editor window opens and the sentiment score for the query string is displayed.

 

image

 

If you want to test further, locate the formula bar and change hello+world  to terrible world and press enter.  Then score should change drastically.  Note, the plus (+) sign is not required when issuing the web service call.  Pretty cool, if I may say so myself.  One more thing, in the top left corner click the Into table icon to convert the result set to a table.

 

If I only wanted to analyze one row of data at a time this would be a great solution, but anyone that works with data knows that it is never just a single row.  So I created a Blank Query in the Query Editor and used the following code:

let
    Sentiment = (inputtext) =>
let
    Source = OData.Feed("https://api.datamarket.azure.com/data.ashx/amla/text-analytics/v1/GetSentiment?Text="&inputtext),
    ToTable = Record.ToTable(Source)
in
    ToTable
in
    Sentiment

What I have done is parameterized the web service call using a function in the Query Editor.  By invoking the function you can manually provide your own text for analysis.  Instead of manually entering a single set of data, I created an excel spread that contains several rows of text and imported that data into Power BI.  The following is a sample set of the data that I used:

 

image

 

The first row is the header and all subsequent rows are the text that need to be analyzed.  After I imported the data I added a custom column to the new table.  I named the column SentimentScore and used the following as the custom column formula:

 

=Sentiment([text])

 

After that I click the expand icon located to the right of the newly created column and unchecked all boxes excepted the one located to the left of the Value column.  Then I clicked OK.

 

image

 

There you go, each row received a sentiment score.  The higher the score the better the sentiment.  Stay tuned for another blog explaining how to perform the key phrase extraction.

 

Patrick LeBlanc

Data Platform Solution Architect

7 thoughts on “Sentiment Analysis with Power BI

  1. […] » Sentiment Analysis with Power BI – … – I was recently asked by a customer to help them do some Text analysis of course evaluation data. They wanted to perform sentiment analysis and key phrase extraction. […]

  2. Permalink  ⋅ Reply

    Karishma

    September 19, 2016 at 9:18am

    Wonderful Article!!! Helped me a lot.
    But Its giving me an error that ..’Sentiment’ not recognized … Can you help ?

    • Permalink  ⋅ Reply

      pleblanc

      October 6, 2016 at 12:33pm

      Sorry for the delay. What did you name the function. please feel free to send me a copy of the PBIX to Patrick@sqllunch.com. Happy to take a look.

  3. Permalink  ⋅ Reply

    Mike

    September 30, 2016 at 11:28am

    hi Patrick – great post, did you ever publish the key phrase extraction article? Thanks! Mike

Leave a Reply

Your email will not be published. Name and Email fields are required.