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:


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


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):


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


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:



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:

    Source = Marketplace.Subscriptions(),
      lexicon_based_sentiment_analysis/v1/" = 
    Score1 = 
    #"Invoked FunctionScore1" = Score1("I had a good day")
    #"Invoked FunctionScore1"


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

    Source = Marketplace.Subscriptions(),
      lexicon_based_sentiment_analysis/v1/" = 
    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):


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



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.


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


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:


And at last you’ll see the scores themselves:


Here’s the code:

    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"})
    #"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:


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 responses

  1. How fantastic is that…Thanks…Machine learning + PowerBI start getting some really cool potential..I will try tweets….
    Had you tried any other Machine learning service in azure..there are plenty..

    • I looked at a few and I couldn’t connect to them from Power Query, unfortunately. I didn’t check all of them though and there may be others that are accessible.

  2. Pingback: SEO Semantica: What’s up Tuesday #16 | AWDseoweb

  3. 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)?

      • I’m able to access now. It is accessible only to Country/Region United States. I changed that setting under My Account. NB: If you have any active data subscriptions you cannot change the Country/Region. I cancelled all my data subscriptions and then changed this setting.

  4. Pingback: Power BI : Power Query, Machine Learning… Et des Zombies - Blog BI & EPM - Site Home - TechNet Blogs

  5. Great post and blog. I was wondering if your book covers using the “M” language to parse an XML file as data source. If not could you point me in the right direction? Thanks

    • 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.

  6. 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!

  7. Pingback: С миру по нитке, №13 | Things I think about

  8. Pingback: How to Analyze and Report on Your Data - ERP Software Blog

  9. Pingback: How to Analyze and Report on Your Data - Microsoft Dynamics GP Community

  10. 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.

    Whic is the problem ?
    Thank you

    • I resolved, i change the reference wiht #””

  11. Pingback: How to Analyze and Report on your Data | My quest in SQL, BI & Dynamics GP

  12. I am getting this error: Expression.Error: Cannot convert the value “[Function]” to type Function.

    • 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.

  13. 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!

  14. Pingback: Power BI Custom Data Connector For Language Detection, Key Phrase Extraction And Sentiment Analysis – Chris Webb's BI Blog

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: