Group data, aggregation as "All Records", as type Record

[dkay84_PowerBI]

I am following a blog post (http://datachant.com/2016/08/09/sentiment-analysis-power-bi-part-2/) on how to overcome Microsoft Cognitive Services API limit of 1000 records per call.  

I have followed his earlier post where, after pulling in the Tweets, you keep only the top (or bottom) 1000 rows to send to the Cog Services API for sentiment analysis, and this works fine.

Where I am having trouble is where you Group By [Index] with aggregation set to "All Records".  This step he says to customize the step as follows:

=Table.Group(#"Integer-Divided Column", {"Index"}, {{"Data", each _, type table}})

To this new formula:

 = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Data", each GetSentimentResults(_), type record}})

* "GetSentimentResults" is the custom function to send the data to MSFT Cog Services

His blog post shows that instead of a table for each record, it should show as records:

Yet when I try this approach, only the data type icon changes (from table to record), however my actual records still say table and not record like the picture above.

MSFT Cog Services requires records to be sent, not tables, so this is preventing the solution from working.  Does anyone know if this is related to Power BI desktop version?  His blog post is from August and I am using November update.  

Thanks

[ImkeF]

Hm, I'm very surprised that it doesn't work. Did you try it?

You need to feed a table to your function and will be given a record in return.

So the first picture you've posted should be the format of the result after you've executed the new step (2nd step/result) and your second picture should be the place where you start editing your code (1st step).

[dkay84_PowerBI]

The first picture is the picture of what SHOULD happen, according to the blog post, but the second picture is what I get on my end after following the steps.

Even when I change the Group By step to include the function and return type Record, I get tables (even thought the data type icon in the column header changes to record).

[ImkeF]

OK, so my understanding of your problem was wrong: This is after you've modified it to the function.

So then it might be that the return of the web call is different than before.

Pls post a picture of what's in the first table so we can find a way to extract the content.

[dkay84_PowerBI]

My initial table is approx. 4000 records in a single column labeled "text".  Each record is a tweet.  If I use the query editor to trim this table to 1000 records, the Cog Services API call works fine.  Following the blog, I add an index and do the integer divide and group by (all records) steps to get the records grouped.  However, when I try to adjust the group by step as described, it is not returning records, but tables.  I believe this is why I am getting the error (Cog Services needs records not tables).

[ImkeF]

The only way forward I can see here is to have a look into the the table (that should have been a record instead) to see what is returned there.

[dkay84_PowerBI]

As I am familiar with your blog I would be willing to share my pbix directly with you (it has my twitter and cognitive services information embedded) if you would like to look.  I could pm you a onedrive link if you have the desire to try and debug this.

[dkay84_PowerBI · Accepted solution]

@ImkeF figured out the problem.  I did not keep a unique id for each tweet, so when I grouped by index (after integer divide), it was just passing a text column to the Cognitive Service API.  This API requires the text column AND a unique record ID column.