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 biswaranjan Visitor
on ‎11-29-2016 04:52 AM

Hi Gil,

 

I was trying to do a Sentiment analysis on FB data by refering your one of the sample demo video and stuck to run "GetSentimentResuls" query which you have provided. Not able to manage "Source" while selecting the "data" filed is not showing in the drop down, and showing the other query I have created. On selecting that query, getting below error.

 

 

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

 

Here is the code

let

Source = (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"= "[API key]",
                           #"Content-Type"="application/json", Accept="application/json"],
                Content=JsonContent 
            ]),
    JsonResponse = Json.Document(Response,1252)
in
   JsonResponse
in
    Source

 

 

by DataChant Member
on ‎11-29-2016 05:46 AM

Hi @biswaranjan

 

Please enable Fast Combine, in File --> Options --> Privacy, and refresh again.

In addition, if you stumble upon additional errors, read my previous comments for possible solutions.

 

Thank you,

Gil

by shamsuddeenvp Member
on ‎12-19-2016 09:13 PM

Dear @DataChant. Thanks for the nice post. Could ypou pls clarify the below queries. 

 

How can we incrementally load the facebook posts?

 

we can only get the posts of fan pages right?

 

We can use R to get the sentiment score (without using Text analytics API)

 

Br,

Shams

 

by Marek Visitor
on ‎12-21-2016 03:24 PM

Sensational! Thank you, Gil.

by bestebaranz Frequent Visitor
on ‎01-04-2017 06:15 AM

Hi to everybody,

 

I've been reading all your comments but I still can't solve my problem. I'm trying to reproduce this sample but when  I write GetSentimentResults (#"Kept First Rows") then I get this error

 

Expression.Error: We cannot convert the value "let

Source = (Sourc..." to type Function.
Details:
Value=let

Source = (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"= "dd1b0fa946e74de5bddc7a320817e89d",
#"Content-Type"="application/json", Accept="application/json"],
Content=JsonContent
]),
JsonResponse = Json.Document(Response,1252)
in
JsonResponse
in
Source
Type=Type

 

I've checked that I haven't got any blank line, the API key is not in brackets etc...

 

Can you help me to solve it?

 

Thanks a lot

by DataChant Member
on ‎01-04-2017 02:18 PM

Hi @bestebaranz,

 

The M expression that you shared is encoded, and cannot be easily read. My immidiate guess is that the last line "Type=Type" should be deleted.

 

Can you share the PBIX file (e.g. on DropBox/OneDrive)?

 

 

by anthonyngha Visitor
on ‎01-08-2017 11:50 PM

@DataChant Interesting work. The number of reactions was pulled from the Facebook Page Insights API?

by acanepa Member
on ‎01-10-2017 09:18 AM

This is great!. Thanks a lot!.

 

Regards,

by DataChant Member
on ‎01-16-2017 08:47 PM

@anthonyngha no, I used posts reactions.

by jaikumar1585 Frequent Visitor
on ‎01-19-2017 12:18 AM

Hi Gil,

 

I am getting "user not authorized" error. Could you please let me know how to resolve this.

 

Regards,

Jai

by DataChant Member
on ‎01-19-2017 04:05 AM

Hi @jaikumar1585,

 

It is related to your API key. Could you ensure you don't have square brackets that wraps the key?

iYou may try to reset your key, if you don't have brackets.

 

Please share the M code, if my tips didn't help.

Thank you,

Gil

by jaikumar1585 Frequent Visitor
on ‎01-19-2017 05:17 AM

Thanks Gil. It resolved the issue.

 

Regards,

Jai

by eljugador Visitor
on ‎03-06-2017 11:13 AM

@DataChant Gil, would you please tell me how you can display the images in the URLs into the dashboard? you are using them as a slicer as well. I wanna know how you do that plz! Also, a .rar file from your blog has a password to extract. What is the password?

 

Thank you! Great post

by sudeep Visitor
on ‎03-22-2017 07:37 AM

 

 

error_sentimental_rslt.png

Hi,

 

I am trying to solve the same issue, but still getting error, do you mind to write a bit more how you solved this problem please.

 

An error occurred in the ‘’ query. DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details:
DataSourceKind=Web
DataSourcePath=https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment

 

Thanks in advance,

Sudeep

by martynhale Frequent Visitor
on ‎05-08-2017 02:00 AM

Hi,

 

I'm getting an error when I change to: 

= GetSentimentResults(#"Kept First Rows")

The error is:

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

 

I've looked through your various responses but can't see anything related.

Would appreciate your assistance.

 

Best wishes,

Martyn

by DataChant Member
on ‎05-08-2017 03:58 AM

Hi @martynhale

You should go to File --> Options & Settings --> Options, and in Privacy section of the current reporrt, check the box that starts with "Ignore Privacy Levels...". Then refresh the report.

by KarineLago Frequent Visitor
on ‎05-30-2017 07:53 PM

AMAZING, Gil! Thank you very much.

by shamsuddeenvp Member
‎06-15-2017 02:42 PM - edited ‎06-15-2017 02:44 PM

Dear @DataChant,

 

Thanks for the wonderful post.

When I publish the file in power bi service, I am nt able to do refresh, i am able to enter credential for FB link. However not able to login to tet analytics api with "Aninymous" login. However I a able to login from power bi desktop.

 

Could you please help me on this?

 

thanks

Shams

 

FBSentimentsRefreshIssue.png

by vaishnavi Visitor
on ‎07-04-2017 06:44 AM

I want to use basic authentication for sentiment analysis instead of anonymous.

Is there any way around for that?

by ramonviaceli Visitor
on ‎07-05-2017 08:27 AM

Hello,

 

I'm trying to get the reactions from facebook and everytime I get this following error:

 

DataSource.Error: Facebook: (#17) User request limit reached

 

How do I fix it?

 

Thanks!

by ilacolu Visitor
on ‎09-13-2017 07:01 AM

I have a problem in this passage : 

Cattura.JPG

 

when I can't call the function GetSentimentResults because there are an error.. Help me please

by shebya Visitor
on ‎09-13-2017 01:12 PM

Hi,

 

This is a terrific post and appreciate taking the time to put together the content for the community.

 

I exactly followed the steps and it worked up until the last step.

 

When I invoked the Custom Function to call Microsoft Text API, I am getting the following error:

 

Appreciate some insights on how I can resolve this issue.

 

Thanks

 

 

PowerBIerror.jpg

by Tech Visitor
on ‎10-10-2017 12:43 PM

I am getting this error when trying to connect

"We couldn't authenticate with the credentials provided. Please try again.

 

Screenshot (16).png

by kapil512 Regular Visitor
on ‎11-05-2017 11:35 AM

HI,

 

I am geeting the below error, can you please me how to resolve?

 

image.png

by rRogick Visitor
on ‎11-05-2017 02:24 PM

@DataChant

 

I'm having the error below and I do not encounter the problem. (problem with credentials)

 

Would you help me?

 

I have already looked at the code, I switched to a simple table (with and without language), I checked my key.

 

 

table.jpgtable xlsxcode.jpgcodeerror.jpgerror

by TaherBouzid Frequent Visitor
‎11-05-2017 03:31 PM - edited ‎11-05-2017 03:33 PM

Hi DataChant

Thank you for this post about sentiment analysis with power bi . I really like your post and I had followed carefully your steps but I still have problems (We couldn't authenticate with the credentials provided. Please try again. ) to connect to service cogntive with api key . ( I even used the second key but doesn't  work )

For the config about data source options :  I switched to ignore and doesn't change any thing .

I thought that can be a problem with this url https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment 

does it always work in your power bi ? 

please I need your hep Smiley Sad 

 

 

 

 Capture.PNG

by DataChant Member
on ‎11-05-2017 03:46 PM

Since Friday I encountered constant errors (Forbidden access) when I tried to use the Text Analytics APIs. I submitted a ticket to Microsoft. Hope it will get fixed soon.

by TaherBouzid Frequent Visitor
‎11-05-2017 03:48 PM - edited ‎11-05-2017 03:51 PM

So that is why it doesn't work for me ?

because even I followed other tutorials ( like keyphrase) but the same result apperead.

by DataChant Member
‎11-05-2017 04:24 PM - edited ‎11-05-2017 04:27 PM

@TaherBouzid Exactly. Sorry. Very annoying experience. I even tried to reset the keys, testwe  different regions, tried two different Azure tenants. Same error. I also had the same issue from a client, where we implemented the same technique.

 

@rRogick@kapil512, and everyone who see this comment - The Text Analytics API is not working for me since Friday. I encourage you to open support tickets to Microsoft, if you see this error.

by tsep Visitor
on ‎11-05-2017 08:49 PM

Credential errors for Text Analytics APIs. Any idea??

 

by kapil512 Regular Visitor
on ‎11-09-2017 08:06 AM

 Is this  API is working?

 

 

Can anyone please let me know.

 

Thanks,

Kapil

by TaherBouzid Frequent Visitor
on ‎11-12-2017 02:33 PM

Hello again @DataChant  : Did you receive a respond from Microsft ? did you solve the problem ? 

Actually I received an email but from Support Professional who will be working with you on this Service Request and tel me  : 

  • Please sign up for Pay-As-You-Go subscription on your account ******************* since your Free Trial is blocked(which cannot be unblocked manually)

https://azure.microsoft.com/en-us/offers/ms-azr-0003p/

 

I think that I must enter my visa  card code to get a subcription  ? ! :/ 

 

by stellar100 Frequent Visitor
on ‎11-17-2017 09:13 AM

Hi Gil, when I followed the fx replaced by = GetSentimentResults(#"Kept First Rows") step. I got a different error saying the function wasn't recognized. Any thoughts?

Capture.JPG

Thanks,

by DataChant Member
on ‎11-17-2017 09:29 AM

@stellar100 did you save the function? If you did, check the Queries pane and make sure you used the same name.

by ashok_kalra Visitor
on ‎11-21-2017 06:58 PM

Hi DataChant,

 

I am still getting Authenticztion error even after changing the privacy settings of the data.

 

please help

by DataChant Member
on ‎11-21-2017 08:40 PM

@ashok_kalra Can you share a screenshot of the error? Do you by any chance use the free tier of Text Analytics? Please check my blog post here.

by shamsuddeenvp Member
on ‎11-23-2017 03:37 AM

Dear @DataChant

 

I am getting the below error when i am reffreshing the facebook post.  I am using below when i connect to facebook connector. It was working before, seems the output fields got changed recently. Could you pls help if you have any info.

 

= Facebook.Graph("https://graph.facebook.com/v2.2/PageName/posts?limit=100")

 

 

FBRefreshIssue.png

by ashok_kalra Visitor
on ‎11-26-2017 10:33 PM

@DataChant Yes. I use free version and this is the first time I am using it so I am sure I haven't exhausted free version limit.

 

 Power BI API issue.JPG

by stellar100 Frequent Visitor
‎11-30-2017 07:28 AM - edited ‎11-30-2017 12:29 PM

 

 

Hi Gil,

Thanks for your post. I'm getting an error 404 Resource Not Found message when invoke the GetSentimentScore function.  

 

An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from 'https://westcentralus.api.cognitive.microsoft.com/text/analytics/v2.0' (404): Resource Not Found
Details:
DataSourceKind=Web
DataSourcePath=https://westcentralus.api.cognitive.microsoft.com/text/analytics/v2.0
Url=https://westcentralus.api.cognitive.microsoft.com/text/analytics/v2.0

 

 

Does this mean something wrong with this endpoint? Please help.

Thanks ahead.

 

 

Solved:

When creating the GetSentimentResults function, instead of pasting the endpoint given directly by Microsoft, adding /sentiments at the end...

(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://westcentralus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"= "[API KEY]",
                           #"Content-Type"="application/json", Accept="application/json"],
                Content=JsonContent 
            ]),
    JsonResponse = Json.Document(Response,1252)
in
    JsonResponse

 

 

 

by Marius1 Visitor
on ‎11-30-2017 11:03 AM

Hi Guys!

 

 

Spent hours trying to fix the "We couldn't authenticate with the credentials provided. Please try again" issue and finally found it, so I wanted to post here and help you.

 

 

The main thing was to set the same location in Power BI option that you have set when getting an API key via web. Then change the privacy settings as in the screenshots attached.

 

Also make sure that in the data source settings, that the file/document permission is public and server's credential is Anonymous and privacy level public.

 

In addition, make sure that the API key is not in the brackets and the URL is correct.

 

All above worked for me, so I am hoping it works for you too.

 

Thanks

 

screenshot11.PNGscreenshot2.PNGscreenshot 3.PNGscreenshot4.PNG

by SoniaL Member
on ‎12-10-2017 09:47 PM

Hi @@DataChant

 

I've followed all your steps (very closely!) but am having trouble with authentication - I keep getting the error message ' we couldn't authenticate with the credentials provided. Please try again. 

 

Any ideas?

 

Thank you for your help!

 

Sonia

 

by SoniaL Member
on ‎12-10-2017 09:55 PM

Hi @Marius1,

 

I'm having the same issue, but unfortunately it's still not working!

 

what do you mean by "The main thing was to set the same location in Power BI option that you have set when getting an API key via web"??

 

Thanks for your help!

 

Sonia

by DataChant Member
on ‎12-10-2017 09:59 PM

@SoniaL can you share the M code that uses Web.Contents? You may wrongly use the API key. Also, try to reset the data source in Data Source Settings, and refresh again.

 

by ketan10 Regular Visitor
‎12-10-2017 11:53 PM - edited ‎12-11-2017 12:07 AM

Hi @DataChant@Marius1@Marius1 I have gone through all the steps in the blog in detail however I get the following issue
Capture.JPG

Could you please help me out ? I am stuck on this issue for a long time Smiley Sad Tried various forum solutions to no avail.

by DataChant Member
on ‎12-11-2017 04:17 AM

@ketan10 did you remove the permissions in the Data Source Settings and try to refresh? You will need to select Anonymous access after you reset the data source.

 

Did it work at some point? If not, can you share the M code of your function?

 

Can you check if your subscription is still active on Azure?

by SoniaL Member
on ‎12-11-2017 01:35 PM

Hi @DataChant

 

See below the code I used - is that what you're after?

 

(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"= "XXXXXXXXXXXXXXXXXXXXXXXXX",
#"Content-Type"="application/json", Accept="application/json"],
Content=JsonContent
]),
JsonResponse = Json.Document(Response,1252)
in
JsonResponse

 

 

 

I've also made sure all the privacy setting are 'ignored'. How do I reset the data source?

 

Thanks for your help, looking forward to having this resolved!

 

Sonia

 

 

by SoniaL Member
on ‎12-19-2017 02:04 PM

Hi @DataChant

 

Any further thoughts on this?

 

I've tried everything and can't seem to fix! 

 

Thanks for your help

 

Sonia

by DataChant Member
on ‎01-05-2018 10:33 AM

@SoniaL you can try to reset the data source by clicking File -> Options and Settings -> Data Source Settings, and clear the credentials for the Text Analytics url. Then try again.

You can send me your report by email (gilra@DataChant.com)

by TaherBouzid Frequent Visitor
on ‎02-18-2018 02:33 PM

Hello @DataChant 

Did you solve the problem  ? 

I had  seen you comment and I thought that you solve the problem ! ? 

@Marius1  thank you for  your respond but even I select these settings the same issue resist . although I created an other account ==> new api key.

This issue has resisted since nov 2017