Sentiment Analysis In Excel With Azure Machine Learning And Power Query

You may have seen Joseph Sirosh’s blog post last week about the ability to publish Azure Machine Learning models to the Azure Marketplace, and that MS have published a number of APIs there already. There’s a new Excel add-in that can be used with these APIs but I noticed that at least one of them, the Sentiment Analysis API, can be used direct from Power Query too.

To do this, the first thing you need to do is to go to the Azure Marketplace, sign in with your Microsoft account, and subscribe to the Lexicon Based Sentiment Analysis API. The docs say you get 25000 transactions free per month although there doesn’t appear to be a way to pay for more; that said the number of transactions remaining shown on my account kept resetting, so maybe there is no limit. The API itself is straightforward: pass it a sentence to evaluate and it will return a score between –1 and 1, where 1 represents a positive sentiment and –1 is a negative sentiment. For example, the sentence “I had a good day” returns the value 1:

image

…whereas the sentence “I had a bad day” returns –1:

image

You can now go to Power Query and click From Azure Marketplace (you will need to enter your credentials at this point if this is the first time you’ve used the Azure Marketplace from Power Query):

image

…and then, when the Navigator pane appears, double-click on Score:

image

The API is exposed as a function in Power Query (I guess because it’s an OData service operation, but I’m not sure) and double-clicking on Score invokes the function. You can enter a sentence here and the Query Editor will open to show the score:

image

image

However, to do anything useful you don’t want to invoke the function just yet – you want a query that returns the function. To do this you need to edit the query. Go to the Advanced Editor and you’ll see the M code for the query will be something like this:

let
    Source = Marketplace.Subscriptions(),
    #"https://api.datamarket.azure.com/data.ashx/aml_labs/
      lexicon_based_sentiment_analysis/v1/" = 
      Source{
       [ServiceUrl="https://api.datamarket.azure.com/data.ashx/aml_labs/
       lexicon_based_sentiment_analysis/v1/"]
       }[Feeds],
    Score1 = 
       #"https://api.datamarket.azure.com/data.ashx/aml_labs/
       lexicon_based_sentiment_analysis/v1/"
       {[Name="Score"]}[Data],
    #"Invoked FunctionScore1" = Score1("I had a good day")
in
    #"Invoked FunctionScore1"

 

You need to remove the last line (called #”Invoked FunctionScore1”) which invokes the function, leaving:

let
    Source = Marketplace.Subscriptions(),
    #"https://api.datamarket.azure.com/data.ashx/aml_labs/
      lexicon_based_sentiment_analysis/v1/" = 
      Source{[ServiceUrl="https://api.datamarket.azure.com/data.ashx/aml_labs/
      lexicon_based_sentiment_analysis/v1/"]}[Feeds],
    Score1 = #"https://api.datamarket.azure.com/data.ashx/aml_labs/
      lexicon_based_sentiment_analysis/v1/"
      {[Name="Score"]}[Data]
in
    Score1

 

You can now click the Close and Load button to close the Query Editor window; you now have a function called Score that you can call in other queries. For example, take the following Excel table (called Sentences):

image

Click on the From Table button to load this table into Power Query, then in the Query Editor click the Add Custom Column button and add a new custom column called Sentiment Score with the expression

Score([Sentence])

image

You’ll then be prompted to set a data privacy level for the data you’re using, because calling this function involves sending data from your worksheet up to the API where someone could potentially see it.

image

Click the Continue button and set the privacy level for the workbook to Public, so that this data can be sent to the API:

image

Click Save and you’ll see the sentiment score has been added as a column containing a Record value. Click on the Expand icon in the SentimentScore column then OK:

image

And at last you’ll see the scores themselves:

image

Here’s the code:

let
    Source = Excel.CurrentWorkbook(){[Name="Sentences"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "SentimentScore", each Score([Sentence])),
    #"Expand SentimentScore" = 
       Table.ExpandRecordColumn(#"Added Custom", "SentimentScore", 
       {"result"}, {"SentimentScore.result"})
in
    #"Expand SentimentScore"

 

You can download the sample workbook here.

Of course, I couldn’t resist repeating this exercise with all of my Facebook status updates – which, of course, can be accessed from Power Query very easily. Here’s my monthly average sentiment score from June 2009 to now:

image

As you can see, I was in a particularly good mood this August – probably because I was on holiday for almost the whole month.

29 thoughts on “Sentiment Analysis In Excel With Azure Machine Learning And Power Query

  1. I have a Microsoft account and Office365 organisation account both based in UK. When I tried to access Lexicon Based Sentiment Analysis API – I get “Not available in your market”. Are you using different market (country/region)?

    • The book does give an introduction to M, and there is a description of how Power Query treats XML, but there aren’t any specific M examples for XML. However once you’ve learned M you should be able to work out how to do what you need to do with your XML file.

  2. Hi Chris, I was reading some of the M language documentation and I was wondering if you knew any code that can auto load an invoked function or automatically rename the function through M (for referencing purposes).

    Essentially, we’d like to have a function that takes an employees name as a parameter and returns one row with all of their relavent information and reference those values in other worksheets so they can auto populate once the function is invoked.

      • So the main query hosts all information for all employees, we’d like to have it so someone can invoke a function one a specific person, and once that function is invoked (returning 1 record), we can reference all of those values into a separate worksheet that populates a template. I think the load to worksheet option will work fine as you mentioned. I was just curious if there were functions to perform renaming of functions, queries, or specifying the load options through M.

        I also saw some try/catch documentation but wasn’t able to figure out how I can use it to perform validation when the function is invoked. So, if they enter an employee name that doesn’t exist, it will come back with an error message. I just realized the name parameter is case sensitive, just like M itself! I think a reminder message the user would be helpful in this case. Thanks for the help!

  3. Hi Chris, i try to use Power Query, but whne i Invoke a Azure sentiment analysis, i have this error:
    Expression.Error: The key did not match any rows in the table.
    Details:
    Key=lexicon_based_sentiment_analysis
    Table=Table

    Whic is the problem ?
    Thank you

    • I resolved, i change the reference wiht #”https://api.datamarket.azure.com/data.ashx/aml_labs/lexicon_based_sentiment_analysis/v1/”

  4. I am getting this error: Expression.Error: Cannot convert the value “[Function]” to type Function.
    Details:
    Value=[Function]
    Type=Type

    • Which Power Query query are you getting this from? Due to recent changes with the Facebook API (and changes in Power Query to reflect his), the code in the workbook for connecting to Facebook probably won’t work any more.

  5. Do we have any open source API for facebook sentiment analysis, like we have sentiment140 for twitter? I want to do something around Facebook and not quite sure where to start!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s