Analyze text in Excel using Azure Machine Learning and Power Query
You may have seen Joseph Sirosh post last week about the possibility of publishing Azure Machine Learning models in the Azure Marketplace, and that MS has already published a number of APIs. For Excel, there is a special addon that can be used to access these APIs, but I noticed that at least one API ( Sentiment Analysis API ) can be used directly through Power Query .
In order to do this, you first need to go to the Azure Marketplace, log in with your Microsoft account and subscribe to the Lexicon Based Sentiment Analysis API . The documentation says that you have 25,000 transactions per month for free. The API itself is very simple: submit a proposal for evaluation, and you will receive a response from -1 to 1, where 1 means positive tonality and -1 negative. For example, the expression "I had a good day" returns the value 1:
Whereas the expression “I had a bad day” returns -1:
')
Now you can go to Power Query and click “From Azure Marketplace” (you will need to enter your credentials if this is the first time you use Azure Marketplace from Power Query):
And then, when the Navigator panel appears, double click on the Score:
The API is represented as a function in Power Query (I think due to the fact that this is an OData-service operation, but I am not sure), and double-clicking on the “Score” will call the function. You can enter an expression in this field, and the Query Editor opens to display the result of the evaluation:
However, in order to do something useful, you need not just to call the function “right now”, you must make a request that returns a function. To do this, edit the request a little. Go to Advanced Editor and you will see the M-code for the request - it will look something like this:
You need to delete the last line (# "Invoked FunctionScore1"), which calls the function, leaving the following code:
Now you can click “Close & Load” to close the Query Editor window. You now have a “Score” function that you can call from other requests. For example, make the following Excel table:
Click on the “From Table” button to load this table into Power Query, then in the Query Editor, click “Add Custom Column” and add a new column called “Sentiment Score” with a Score ([Sentence])
You will be prompted to set the privacy level for the data you are using, since calling this function involves sending data from your sheet to the API, where, potentially, someone can see it.
Click “Continue” and set the privacy level for this workbook as “Public” so that the data can be sent to the API:
Click “Save” and you will see that the “Sentiment Score” column has been added, containing the value “Record”. Click on the “Expand” icon in this column and then “Ok”:
And finally, you will see, in fact, the assessment:
Here is the code:
You can download the file from the workbook here .
Of course, I could not resist and repeated it with my status updates on Facebook - which can be accessed very easily from Power Query . Here is the average monthly assessment of the tonality of my statuses from June 2009 to the present:
As you can see, I was in a particularly good mood in August - perhaps because I was on vacation for almost the entire month.
In order to do this, you first need to go to the Azure Marketplace, log in with your Microsoft account and subscribe to the Lexicon Based Sentiment Analysis API . The documentation says that you have 25,000 transactions per month for free. The API itself is very simple: submit a proposal for evaluation, and you will receive a response from -1 to 1, where 1 means positive tonality and -1 negative. For example, the expression "I had a good day" returns the value 1:
Whereas the expression “I had a bad day” returns -1:
')
Now you can go to Power Query and click “From Azure Marketplace” (you will need to enter your credentials if this is the first time you use Azure Marketplace from Power Query):
And then, when the Navigator panel appears, double click on the Score:
The API is represented as a function in Power Query (I think due to the fact that this is an OData-service operation, but I am not sure), and double-clicking on the “Score” will call the function. You can enter an expression in this field, and the Query Editor opens to display the result of the evaluation:
However, in order to do something useful, you need not just to call the function “right now”, you must make a request that returns a function. To do this, edit the request a little. Go to Advanced Editor and you will see the M-code for the request - it will look 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 delete the last line (# "Invoked FunctionScore1"), which calls the function, leaving the following code:
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 Score1 Now you can click “Close & Load” to close the Query Editor window. You now have a “Score” function that you can call from other requests. For example, make the following Excel table:
Click on the “From Table” button to load this table into Power Query, then in the Query Editor, click “Add Custom Column” and add a new column called “Sentiment Score” with a Score ([Sentence])
You will be prompted to set the privacy level for the data you are using, since calling this function involves sending data from your sheet to the API, where, potentially, someone can see it.
Click “Continue” and set the privacy level for this workbook as “Public” so that the data can be sent to the API:
Click “Save” and you will see that the “Sentiment Score” column has been added, containing the value “Record”. Click on the “Expand” icon in this column and then “Ok”:
And finally, you will see, in fact, the assessment:
Here is the code:
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[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 file from the workbook here .
Of course, I could not resist and repeated it with my status updates on Facebook - which can be accessed very easily from Power Query . Here is the average monthly assessment of the tonality of my statuses from June 2009 to the present:
As you can see, I was in a particularly good mood in August - perhaps because I was on vacation for almost the entire month.
Source: https://habr.com/ru/post/247299/
All Articles