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:
[sourcecode language='text' ]
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"
[/sourcecode]
You need to remove the last line (called #”Invoked FunctionScore1”) which invokes the function, leaving:
[sourcecode language='text' ]
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
[/sourcecode]
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
Score([Sentence])
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:
[sourcecode language='text' ]
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"
[/sourcecode]
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.
Reblogged this on Excel B2B and commented:
Sentiment Analysis In Excel
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.
Unfortunately not available in my Market.
Probably don’t wish to analyse my sentiment about it !!
-Regis
Which market are you trying to access it from?
The Danish one….
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)?
No, I have a UK-based Microsoft account. Not sure what’s happening here…
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.
Thanks S.S this did the trick !
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.
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.
Can you give me more of an idea about what you want to do? I take it that it’s not an option to store the employee name anywhere in the worksheet?
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!
You can’t rename functions, but you can have functions that return other functions: http://cwebbbi.wordpress.com/2014/08/24/power-query-functions-that-return-functions/
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/”
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.
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!
Not that I know of, sorry…