PowerBI- Text Analytics (sentiment/opinion mining) stopped working

[adamina14]

Hi all,

A couple of weeks ago (17 Mar), I managed to get PowerBI working with Azure Cognitive Services for sentiment analysis.  While this is a straightforward activity for obtaining an overarching sentiment by comment, I was wanting to use the opinion mining functionality to get some deeper insights. 

I found a great example of how to do this here:  MaheshSQL/CognitiveServicesDemos: Azure Cognitive Services Demos (github.com)

And I implemented a version similar version in my own model which successfully detected sentiment and opinions in about 92/95 records.  I know this is a small number- this is just a proof of concept. 

This same file is now only detecting sentiment and opinions in 7/95 records (26/03).  I feel like I'm going mad.  The remaining records are showing as an error (below). 

An error occurred in the ‘Get Sentiment’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]

Example of results (once extracted) from 17 Mar:

Same results from 27 Mar:

I have:

• created a fresh file and dashboard, with a new Azure resource- both files are now returning the same results (running on S0 Standard pricing tier)
• restored the original file from two weeks ago- as soon as I open Power Query, it's refreshing the data and I get the same outcome
• checked my Azure account status to ensure I haven't over-run my account with queries- it's clear.  I haven't even got a bill for 40c yet which makes sense since I'm working with around 1000 records total across about 20 tables. 
• checked to see whether there has been a model version change (I'm not actually referencing the model version in the query or parameters so it should simply default to the latest version even if this was the problem)
• checked service outage reports (using an Australian hosted location)
• and finally turned in desperation like so many, to the internet to solve my problems. 

I am relatively new to this space so any help would be much appreciated!

Here are the parameters I have defined:

• API Key= xxxxxxxxxxxx
• Language Endpoint = https://xxxxxxxxxxxx.cognitiveservices.azure.com/text/analytics/v3.1/sentiment?opinionMining=true
• Records to Analyse = 500

Here is the query:

_________________________________________________________________________________________

= (text) => let

apikey = #"API Key",
endpoint = #"Language Endpoint",

jsontext = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),
jsonbody = "{ documents: [ { language: ""en"", id: ""0"", text: " & jsontext & " } ] }",
bytesbody = Text.ToBinary(jsonbody),
headers = [#"Ocp-Apim-Subscription-Key" = apikey],
bytesresp = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]),
jsonresp = Json.Document(bytesresp),

sentiment = Text.From(jsonresp[documents]{0}[confidenceScores][negative]) & "|" &
Text.From(jsonresp[documents]{0}[confidenceScores][neutral]) & "|" &
Text.From(jsonresp[documents]{0}[confidenceScores][positive]),

opinions = List.Generate(
()=> 0,
each _ < List.Count(jsonresp[documents]{0}[sentences]),
each _+1,

each
//To check if opinion mining result exists
if Table.RowCount(
Table.SelectRows(Record.ToTable(jsonresp[documents]{0}[sentences]{_}),each Text.Contains([Name], "targets"))
) > 0
then
"{sentence:" & """" & jsonresp[documents]{0}[sentences]{_}[text] & """"
& ", sentiment:" & """" & jsonresp[documents]{0}[sentences]{_}[sentiment] & """"
& ", target:" & """" & jsonresp[documents]{0}[sentences]{_}[targets]{0}[text] & """"
& ", assessment:" & """" & jsonresp[documents]{0}[sentences]{_}[assessments]{0}[text] & """"
& "}"
else //Only return sentiment
//null
"{sentence:" & """" & jsonresp[documents]{0}[sentences]{_}[text] & """"
& ", sentiment:" & """" & jsonresp[documents]{0}[sentences]{_}[sentiment] & """"
& ", target:null"
& ", assessment:null"
& "}"
),

// opinions_combined = "{sentences: [" & Text.Combine(opinions, ",") & "]}"

output = "{
OverallSentiment:" & """" & Text.From(jsonresp[documents]{0}[sentiment]) & """" & ",
confidenceScores:
{
negative: " & Text.From(jsonresp[documents]{0}[confidenceScores][negative]) & ",
neutral: " & Text.From(jsonresp[documents]{0}[confidenceScores][neutral]) & ",
positive: " & Text.From(jsonresp[documents]{0}[confidenceScores][positive]) & "
},
opinionMining:
{
sentences: [" & Text.Combine(opinions, ",") & "]
}
}"

in output

____________________________________________________________________________

Hoping the Azure/PowerBI gods can answer my question!  Thanks for reading. 

[v-yangliu-msft]

Hi  @adamina14 @

That usually means that you are trying to perform operations on an empty table or list. To fix it, go to the query editor and see  which table is not loading; you should see an error. You can review each step to see which is causing the error. 

Have you done any Pivot Columns operations?

This is the related document, you can view this content:

https://community.powerbi.com/t5/Desktop/Expression-Error-There-weren-t-enough-elements-in-the/m-p/4...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adamina14 · Accepted solution]

Solved- the preview version of the model had been versioned out by the looks of it and I was missing an end to the endpoint. 

[adamina14]

Thank you for your suggestion- I'll have a look at this.  I haven't done any Pivot Column operations.