Sentiment Analysis in Power BI

by DataChant Member on ‎08-04-2016 10:35 PM - last edited on ‎08-22-2016 04:28 PM by Power BI Admin Sandy

You may think that Sentiment Analysis is the domain of data scientists and machine learning experts, and that its incorporation to your reporting solutions involves extensive IT projects done by advanced developers. Well, today this is going to change.

 

Today I will show you how to gain Sentiment Analysis insights without the help of machine learning gurus or software ninjas. All you need to do is open your Power Bi Desktop and follow the steps below.

 

A short intro...

For the last six months I have been sharing Power BI scenarios, tips & tricks on my blog DataChant.com blog. One of the most popular scenarios described how to gain insights on your brands and your competitors through their presence on Facebook (Read more here). But I always had an itch to add Sentiment Analysis as new dimension for the insights. For example, with Sentiment Analysis we can better understand competitors' posts, or fans' comments and measure their emotional engagement or track down outliers of negativity in our campaigns.

 

So let's pretend we are the social media analysts of the US Presidential Candidates, and imagine we have a Power BI report like this one to analyze the reactions to Clinton's and Trump's Facebook posts.

 

 

Wouldn't it be cool, if we could also apply Sentiment Analysis on Clinton's and Trump's posts, and correlate the sentiment that arise from their messaging with the reactions they get?  Wouldn't it be awesome if we could drill down the posts by sentiment and high rate of specific Facebook reactions?

 

While you can already apply Sentiment Analysis in Excel using Azure Machine Learning Add-in (read more here), and there are already great posts on doing it the "Power Query" way (for example here), I think that there are many advantages to the technique I will share with you today (For example, you gain better integration in Power BI than with the Add-in; you can post more messages in a single API call; You are using the newest service rather than the soon-to-be deprecated Azure Marketplace),

 

Are you ready to begin?

To perform Sentiment Analysis on Clinton's and Trump's Facebook posts, we first need to pull down their posts. On my blog you can find several techniques to do so. Since our main focus today will be on the Sentiment Analysis part, let's start with an Excel workbook that I prepared in advance: Facebook Reactions Data Sample.xlsx (You can download it from the blog's attachments section).

The Facebook Reactions Data Sample.xlsx workbook contains 500 recent Facebook posts from each of the US Presidential candidates. We will import it to a Power BI Report, and apply Sentiment Analysis on it. Please download this workbook and save it to your computer. We will import it in a minute.

 

Are we going to see here some complex sentiment analysis algorithms?

Of course not. We don't need a mad scientist to help us this time. We will use out-of-the-box Sentiment Analysis API that is already offered for free by Microsoft Cognitive Services.

According to Microsoft, the Sentiment Analysis API "returns a numeric score between 0 and 1. Scores close to 1 indicate positive sentiment and scores close to 0 indicate negative sentiment. Sentiment score is generated using classification techniques. The input features of the classifier include n-grams, features generated from part-of-speech tags, and word embeddings. English, French, Spanish and Portuguese text are supported." (Read more here).

 

So before we start the tutorial itself, let's sign up for Microsoft Cognitive Services here.

 

Screenshot_4.png

After you click the Get started for free button above and follow the easy signup process for Text Analytics Preview, you will reach your account page (here), where you can obtain your Text Analytics API key. Click on the Copy link (as highlighted below) to obtain the key. You will need it soon.

 

 Screenshot_5.png

 

Open Power BI Desktop, click on Get Data drop down menu and select Excel.

 

Screenshot_12.png

 

After you downloaded the attached Facebook Reactions Data Sample.xlsx, select that workbook in the Open dialog and click Open.

 

Screenshot_13.png

 

In the Navigator dialog, select the tables: Candidates, Posts, Reactions and ReactionTypes and click Edit.

 

Screenshot_14.png

 

Now in the Query Editor you can see a preview of the four tables and get a glimpse of the data we have at hand.

 

Our main focus today will be in the technique that allows us to upload the text data to the Sentiment Analysis API and load it to our report. We start by selecting the Posts query which contains the text for analysis. 

Right click on Posts in the left Queries pane, and click Reference.

 

Screenshot_15.png

 

Rename the query Posts (2) to Sentiment Results. To rename the query, right click on it and click Rename.

 

Screenshot_16.png

 

Note: In the next step we will select the columns in our data that contain the actual text for analysis and a unique ID for each text. The unique ID is required by Microsoft Cognitive Services API. The unique ID will help us to map the Sentiment scores in the response to the relevant text. In our data, we use the Facebook Post IDs as unique IDs. If you don't have a unique ID for the text in your own dataset, you can always use Add Column --> Add Index Column to obtain unique ID. 

 

In Home tab of Query Editor, click Choose Columns, unselect all columns, then select Post ID and Message and click OK.

 

.Screenshot_37.png

 

Now, let's rename the columns. Post ID should be renamed to id. Message should be renamed to text. This step is critical. Don't miss it. The Sentiment Analysis API requires these names.

 

Screenshot_17.png

 

Note: If you need to perform analysis on text in French, Spanish or Portuguese, you can create another step here and define a custom column whose name is language and is value is "fr", "es" or "pt" (For French, Spanish or Portuguese). Since English is the default language in the API, we skip this step.

 

In the next step, we will remove rows with empty text messages. There is no point in sending such rows for Sentiment Analysis, and the service will return errors if we try to.

 

Click in the filter icon of the column text, and then click Remove Empty.

 

Screenshot_22.png

 

In the next step we will keep the top 1000 rows. We don't need to do it in our specific dataset that contains 1000 messages. We do it to prevent errors when you try these steps on your own data, since Microsoft Cognitive Services API allows only 1000 text messages in a single API request call.

 

But don't worry, if you have more than 1000 rows stay tuned to my next blog post here with a detailed walkthrough that will also show you how to perform multiple API calls. It was just too long for a single blog post.

 

In Home tab, click Keep Rows, and then click Keep Top Rows.

 

Screenshot_23.png

 

In the Keep Top Rows dialog, set 1000 as Number of rows and click OK.

 

Screenshot_24.png

 

Let's take a short stop, and let the query Sentiment Results have a break. We will return to it soon.

We will now create a new query that sends our data to the Sentiment Analysis service. 

In Home tab, click New Source drop down menu, and select Blank Query.

 

Screenshot_19.png

 

Rename the new query to GetSentimentResults, and In Home tab click Advanced Editor. Copy & paste the code below to the Advanced Editor main box, and then replace the part which is highlighted below in red with the API key that you obtained from Microsoft Cognitive Services (as mentioned above). 

 

(Source as table) as any =>
let
    JsonRecords = Text.FromBinary(Json.FromValue(Source)),
    JsonRequest = "{""documents"": " & JsonRecords & "}",

    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"= "[Paste your API key here]",
                           #"Content-Type"="application/json", Accept="application/json"],
                Content=JsonContent 
            ]),
    JsonResponse = Json.Document(Response,1252)
in
    JsonResponse

 

After you place the API key, click Done in the Advanced Editor.

 

Note: Since you have inserted your API key in the code above, make sure with whom you share the Power BI Desktop file (.pbix). The API key will remain visible. If you don't wish to share the API key, you can use  Power BI template with the API Key as a parameter.

 

Screenshot_38.png

 

Going back to the query Sentiment Results, click on the little fx icon on the formula bar. If you don't see the formula bar you can enable it from the View tab.

 

Screenshot_25.png

 

 Change the formula from:

 

= #"Kept First Rows"

 

To:

 

 

= GetSentimentResults(#"Kept First Rows")

Press Enter, and click Edit Credentials in the yellow business bar. In the Access Web Content dialog, keep the default Anonymous option, and click Connect.

 

 

Screenshot_40.png

 

You will now see a record of documents and errors, Click on the List object of documents.

 

Note: I will provide more details about error handling on a followup blog post here. For simplicity reasons, let;s ignore the few errors that the API returns (If you cannot wait for the followup blog, you can truncate the column text to 10240 characters to avoid those errors).

 

Screenshot_27.png

 

Click To Table in List Tools / Transform tab.

 

Screenshot_28.png

 

Click OK in the To Table dialog.

 

Screenshot_29.png

 

Expand the column Column1 by clicking on the little icon which is highlighted below.

 

Screenshot_30.png

 

Again, expand the column Column1. In the expand column pane, select score and id. Then uncheck Use original column name as prefix and click OK.

 

Screenshot_31.png

 

Right click on the header of column score and select Change Type, then select Decimal Number.

 

Screenshot_32.png

 

In Home tab, click Close & Apply.

 

Screenshot_33.png

 

That's it. With a single API call to Microsoft Cognitive Services, you got 1000 scores between 0 (Negative) to 1 (Positive) for the attached Facebook posts that were made by Clinton and Trump.

 

The next steps are the modeling and visualizations. There is nothing new there, so I will let you try it yourself before I publish a detailed walktrhough. Till I do, you can learn here how to create most of the visualizations below, including a cool Pulse Chart that triggers different events depending on your slicer manipulations.

 

 

 

Would you like to learn more? Follow my blog here where I will soon share with you how I created this dashboard.

You will also learn:

 

  • How to handle multiple API calls with 1000 messages on each call.
  • How to handle errors.
  • How to split the messages to sentences for better accuracy of the Sentiment Analysis alogirthm.
  • How to use DAX and a slicer to dynamically define the Negative, Neutral and Positive Sentiment thresholds.
  • How to corrolate Facebook reactions with Sentiment Analysis scores.

 

Thank you for reading,

Gil

Comments
by mohsenvafa Frequent Visitor
on ‎08-05-2016 04:10 PM

Hi,

Thanks for the great post, I am getting following error when I try to follow the steps provided in above. 

 

Formula.Firewall: Query 'Sentiment Results' (step 'Custom1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

Is there any reason to get this error?

Issue.png

by DataChant Member
on ‎08-05-2016 04:19 PM

Dear mohsenvafa,

Please set Fast Combine to ignore privacy levels. It should resolve this issue.

Here is how you can do it: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-privacy-levels/

by mohsenvafa Frequent Visitor
on ‎08-05-2016 04:27 PM

Thanks, it resolved the issue!

by DataChant Member
‎08-07-2016 07:16 PM - edited ‎08-07-2016 07:19 PM

Check out this Power BI Data Gallery Story: Sentiment Analysis on your comments to Power BI Facebook Page

 

Screenshot_25.png.

by furlong46 Occasional Visitor
‎08-08-2016 06:53 AM - edited ‎08-08-2016 06:55 AM

Good post.  I did a demo of this last week for a webinar using Amazon Product reviews.  I loaded my JSON files to Azure Data Lake Store and parsed them with the JSON serde.  I also parsed a static JSON file with Power BI.  https://www.blue-granite.com/webinar-overview-of-text-analytics-microsoft-azure

by DataChant Member
‎08-08-2016 09:28 AM - edited ‎08-09-2016 07:58 AM

Thank you , furlong46. I am looking forward to watching your webcast.

The fact that we can now perform Sentiment Analysis without external Hadoop and R, and use Power BI Desktop for the entire workflow, makes the solution much more accessible for any Excel / BI end-users. For many, the mention of R or Hadoop is enough to keep a distance, and be dependent on their rare big data specialists to proceed. Self-service BI is really "self" in my case.

by DrBiDuro Frequent Visitor
on ‎08-10-2016 02:32 AM

Hi,

 

many thanks for the great tutorial.

 

Im ama almost through it, but I get the following error I do not understand...

 

 

Captura.PNG

 

 

Any way to solve this? 

 

Many thanks in advance!

BR

by DataChant Member
on ‎08-10-2016 04:03 AM

Hi DrBiDuro,

 

You have a network issue. Could you copy the URL to your browser and see if you can access the domain?

Please paste the URL here in your reply. The screenshot you shared is blurry.

 

by DrBiDuro Frequent Visitor
on ‎08-10-2016 07:32 AM

Thanks for the quick reply DataChant!!!!

 

I finally resolved this issue it was indeed a type mistake in the URL and now I get the data perfectly.

 

Now I wanted to apply this method to an internal response database I have. I followed the exact same steps but I get Error 400 Bad request...  Any thoughts?

 

"An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from 'https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment
Url=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment"

 

Again, thanks for your amazing work!

by DataChant Member
on ‎08-10-2016 08:03 AM

Thank you DrBiDuro.

 

Is there any chance you have more than 1000 messages? Try to keep only the top 1000 rows and try again. I am publishing soon here how to handle messages beyond the first 1000. 

by DataChant Member
on ‎08-10-2016 09:53 AM

Update:

Follow DataChant next blog post for Part 2 of this article, and learn how to handle how to refine the solution beyond 1000 messages.

by JPC3 Frequent Visitor
on ‎08-11-2016 01:43 AM

DrBiDuro,

 

How did you resolve your URL type mistake?  I am getting the same error and cannot see where I am going wrong?

 

Many thanks

by DrBiDuro Frequent Visitor
on ‎08-11-2016 02:04 AM

Hi JPC3,

 

I was missing the question mark at the end of the URL. Nevertheless, I think that I actually solved it doing the following:

 

When editing credentials (where you select anonymous autentication), y chose to the more detailed URL, which was "https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment" instead of ""https://westus.api.cognitive.microsoft.com". You can select the URL in this step.

Hope I was helpfull.

 

by DrBiDuro Frequent Visitor
on ‎08-11-2016 02:06 AM

Hi DataChant,

 

yes, I actually filtered top 900 rows to make sure this was not the mistake but i get the same error...

 

I willl review your next post and see if there is a solution.

 

Thanks!

by DataChant Member
on ‎08-11-2016 04:42 AM

The issue that DrBiDuro and JPC3 report can be fixed. If you applied the #"Kept First Rows" step, please change the line:

= GetSentimentResults(#"Filtered Rows")

To:

= GetSentimentResults(#"Kept First Rows")

 

The error only happens when you feed the function with a table which is longer than 1000 rows.

 

by DrBiDuro Frequent Visitor
on ‎08-11-2016 06:18 AM

Datachant, thanks a lot, finally I solved the issue.

 

Apparently, even when selecting "Remove Blanks", some fields with empty spaces remain, and this is why the API returns Error400. A narrower filter is needed and now everithing works fine.

 

Thanks!

by shamsuddeenvp Member
on ‎08-20-2016 03:24 AM

 

Nice one. Thanks @DataChant.

 

I am having a issue when I tried the same to fo sentimental analysis for a page. Can you pls help where I am doing mistake.

 

Br,

Shamsuddeen

 

CredentialIssue_Senti.png

by DataChant Member
on ‎08-20-2016 03:44 AM

Hi Shamsuddeen,

 

Any chance you kept the brackets outside the API key?

Try it without the brackets.

 

 

by shamsuddeenvp Member
on ‎08-20-2016 06:55 AM

thanks @DataChant. Yes, my istake, bracket was the issue.

 

by webportal Regular Visitor
on ‎08-22-2016 03:10 PM

Hi, 

After changing the formula in Sentiment Results to: 

= GetSentimentResults(#"Filtered Rows")

I get the error:

Formula.Firewall: Query references other queries, so it may not directly access a data source.

And I get stuck here...

Can anyone help?

Thanks a lot!

by DataChant Member
on ‎08-22-2016 03:24 PM

Hi webportal,

 

Please go over my comments above. You will find the answer Smiley Happy

by shamsuddeenvp Member
on ‎08-28-2016 03:53 AM

Dear @DataChant

 

We can use the API key only once? whe I tried to use the same function "GetSentimentResults" on top of other texts (user comments), I am getting error stating that, query is not processed, issue with the key".

 

Br,

Shamsuddeen

by DataChant Member
on ‎08-28-2016 05:12 AM

Hi Shamsuddeen,

 

It is unlikely the issue here. I have used the same key on many different queries. I can only guess that you have empty texts. That could explain the error.

You may also use more than 1000 rows. You can follow the part 2 of this article here to process more than 1000 text messages.

If you wish, you can contact me by email with the details: gilra@datachant.com

by mmoizk Regular Visitor
on ‎08-28-2016 02:29 PM

Hi DataChant,

I don't have 

= #"Kept First Rows" in Sentiment Results
Instead i have = Table.FirstN(#"Filtered Rows",1000)
by Baskar New Contributor
‎08-29-2016 12:54 AM - edited ‎08-29-2016 01:03 AM

Solved

by DataChant Member
on ‎08-29-2016 07:11 AM

Hi mmoizk,

 

I am not sure I understand your issue. Could you please provide more details?

by mmoizk Regular Visitor
on ‎08-29-2016 10:32 AM

Sure @DataChant, What i was trying to say is during my hands on exerseice of this, at the step  where we are trying call getSentiment  as below

Change the formula from:

 

= #"Kept First Rows"

 

To:

 

 

= GetSentimentResults(#"Kept First Rows")

 in my data set i don't see = #"Kept First Rows" but i see Table.FirstN(#"Renamed Columns",1000) in my the formula box. 

So i was wondering why i have Table.FirstN ?

by DataChant Member
on ‎08-29-2016 11:34 AM

Hi mmoizk

 

I think you are doing alright, and sorry for not being clear.

For clarification, here is a short explanation of what you see, and in the second paragraph, I explain what you should do next.

The #"Kept First Rows" is the name that was given for your last step, where you applied Table.FirstN. Check out the Applied Steps left pane. You will see that your last step is called "Kept First Rows". If you open the Advanced Editor will see this line at the bottom:

#"Kept First Rows" = Table.FirstN(#"Renamed Columns", 1000)

So all is good.

The next step is to click the fx command near the formula bar. This will create a new step

= #"Kept First Rows"

And you will be able to change it as I wrote above.

by DataChant Member
on ‎09-07-2016 10:34 AM

I published Part 3 of this article. Read it here.

Screenshot_19.png

by ado Visitor
on ‎09-20-2016 07:00 PM

Great post. Nice and clear. Thanks @DataChant!!!

by Dawngrrl Visitor
on ‎10-18-2016 03:50 AM

Hiya - awesome post thanks so much.  I have everything set it seems, but I get user not authorized when I run it - checked that its set to anon.  Thoughts?

 

Thanks!

Dawn

 

by martar Visitor
on ‎10-19-2016 04:41 PM

I have tried all the above and still get the error message:

 

An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from 'https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment' (400): Bad Request
Details:
    DataSourceKind=Web
    DataSourcePath=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment
    Url=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment

 

 

Could you please help?

 

Thank you!

by DataChant Member
on ‎10-19-2016 05:01 PM

Hi Martar,

 

Please confirm that you don't have duplicate keys, don't use more than 1000 messages per call, and that there are no blank or null values in the column "text". You can use the Query Editor to remove duplicates and empty values.

 

Hope it helps,

Gil

by martar Visitor
on ‎10-20-2016 01:45 AM

Tried all that and still getting an error...any ideas anyone?

 

Marta

by DataChant Member
on ‎10-20-2016 06:04 AM

Hi Marta,

 

 

Can you send me your PBIX file, or share here the screenshot of the Query expression (from Advanced Editor)? You can mask the API key.

 

My email is gilra@datachant.com

 

Thank you

Gil

 

by martar Visitor
on ‎10-20-2016 08:10 AM

Hi Gil,

 

I'm on my way to the airport and on leave for a month but it'd be great if we could maybe talk on Skype when I'm back (Nov 14th). My email is martar@microsoft.com.

 

thank you!

 

Marta

by Evogelpohl Member
‎10-26-2016 02:59 PM - edited ‎10-26-2016 03:06 PM

Thanks @DataChant

 

Instead of starting with a table, as you did with FB; I'm trying to simply create a Function in PowerBI/Power Query using a slight modification of your approach.

 

This should allow me to create a custom column with:  try fxSentiment(my-tweets) and get as many back as I have tweents in my column.

 

I'm getting an error: "Web.Contents failed to get contents from 'https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment' (400): Bad Request"

 

Thoughts?

 

let

  TweetCognitive = (TweetText as text) =>

let
    JsonRecords = Text.FromBinary(Json.FromValue(TweetText)),
    JsonRequest = "{""documents"": " & JsonRecords & "}",

    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"="my_key",
                           #"Content-Type"="application/json", Accept="application/json"],
                       Content=JsonContent 
            ]),
    JsonResponse = Json.Document(Response,1252)
in
    JsonResponse

in
    TweetCognitive

 

by DataChant Member
on ‎10-26-2016 05:00 PM

Hi Evogelpohi,

 

Did you intend to pass a single text message to this function?

The function requires a table with the columns text and id. The id must be a unique key.

by Evogelpohl Member
‎10-26-2016 06:25 PM - edited ‎10-26-2016 06:31 PM

@DataChant Yes, just the TweetText, though i have the original tweet ID as well that I could pass.  I'm not sure I'm aware of passing both. 

by Evogelpohl Member
‎10-26-2016 07:52 PM - edited ‎10-27-2016 10:14 AM

Wow, I can't wait until the Cortana Cognitive API Services come out for PowerQuery natively.  But, I think i got it.  

 

let 
TweetCognitive = (TweetID as text, TweetText as text) =>
let
JsonRecords = Text.FromBinary(Json.FromValue([id=TweetID, text=TweetText])),
JsonRequest = "{""documents"": [" & JsonRecords & "]}",
JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),

Response = Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
[ Headers = [#"Ocp-Apim-Subscription-Key"="11111111111111111111111111111111",
#"Content-Type"="application/json", Accept="application/json"],
Content=JsonContent ]),

JsonResponse = Json.Document(Response,1252)
in
JsonResponse
in
TweetCognitive

 

by brycegado Visitor
on ‎11-16-2016 11:43 AM

HI @DataChant Caan you please give me the link to the Facebook Reactions Data Sample? i cannot find it on the blog And also is there a chance to show us how you got the data from the facebook pages so we can replicate this on other pages? Thanks

by DataChant Member
on ‎11-16-2016 11:52 AM

Hi @brycegado,

 

The sample file is located here, it is also "attached" to the bottom of the article itself (above the comments).

 

I have never shared how I collect the reaction data from Facebook. It is done 100% in Power BI / Power Query using advanced Graph API techniques. I share it with my customers Smiley Happy

Feel free to contact me at gilra@datachant.com if you are intersted.

by systamper Member
on ‎11-18-2016 01:45 PM

Hi,

 

I followed the directions, but when I get to editing the credentials for the API, I receive an error saying "The user was not Authorized." I read the comments above, but do not see this resolved.

 

Any thoughts or ideas would be most appreciated!

 

Thanks,

Scott

 

 

 

 

by aditm Visitor
on ‎11-21-2016 02:10 AM

[DataFormat.Error] Invalid cell value '#NAME?'. 

Got this error, when performing sentiment analysis on text beyond 1000 messages

by DataChant Member
on ‎11-21-2016 04:56 AM

Hi @aditm

 

You can follow Part 2 of this blog post here to cover more than 1000 messages, and resolve the issue above.

Thank you for reading Smiley Happy

Gil

by DataChant Member
on ‎11-21-2016 04:59 AM

Hi @systamper

 

If deleting the credentials and setting the web access to anonymous didn't help, I suggest that you ensure you follow the steps correctly. Can you paste the M expression from the advanced editor here? Make sure you didn't keep the brackets in the code.

 

Thank you,

Gil

by pinikr Frequent Visitor
on ‎11-21-2016 05:41 AM

Hi

Thanks for the Post.

2 Questions:

1. if i do not have ID field? is in mandatory?

2. i have lots of langs - so i have added translated column - now i haver undocumnted error - what should i do?

by DataChant Member
on ‎11-21-2016 06:06 AM

Thank you @pinikr for the great questions.

1. If you don't have ID field, you should create one. The reason you need it, is that the API returns the results in pairs of ID and Sentiment score. The ID will help you to map the score to the text. Theoretially, you can duplicate the text and send it as an ID, but this will probably fail and will not be efficient method. A better approach is to apply an index as a new column. Simply click Add Column --> Index Column --> From 0 or From 1. Then rename the column Index to id.

2- So you now have a translated column. I assume that in that column, all your messages are in English. You will now need to follow these steps:

2.1 - Click Add Column --> Custom Column, set language as the column name, and add the formula = "en". 

2.2 - Remove the original text column with the multiple languages, and rename the column with English messages to text.

That's it, you are ready to go. In case you wish to display the original multi-langauge messages, make sure that all these changes are done in a new query (which is referencing your original query with the multi-language messages), so you'll not lose those messages.

 

Hope it helps,

Gil

 

 

by systamper Member
on ‎11-21-2016 06:17 AM

Thanks, Gil (@DataChant). It was actually the brackets that did it. So yeah, I am an idiot. :-)

 

Next up, I get the following error when I save the step to call the GetSentimentResults function:

An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from 'https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment
Url=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment

 

Thanks for your help!

Scott

 

And here is the function I am trying to call:

(Source as table) as any =>
let
JsonRecords = Text.FromBinary(Json.FromValue(Source)),
JsonRequest = "{""documents"": " & JsonRecords & "}",

JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
Response =
Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
[
Headers = [#"Ocp-Apim-Subscription-Key"= "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
#"Content-Type"="application/json", Accept="application/json"],
Content=JsonContent
]),
JsonResponse = Json.Document(Response,1252)
in
JsonResponse

 

 

by DataChant Member
on ‎11-21-2016 06:22 AM

Hi @systamper

 

You are good. Make sure you follow these steps:

  1. Remove empty values in column text.
  2. Remove Duplicates in column id.
  3. Have a custom column with values en in column language.
  4. If you have more than 1000 rows, follow Part 2 of this tutorial in my blog here.

Hope it helps,

Gil