cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martynhale Frequent Visitor
Frequent Visitor

How to disable an api call?

Hi all,

 

I've created a data model which uses a function to call the Microsoft Sentiment Analysis api - everything works really well.

 

What I want to do is somehow disable the call where records have already been allocated a sentiment score from a previous call. This is because there's a cost per call - once I have a score, I don't need to recall the api.

 

 

Below is the function called Sentiment:

 

let
Sentiment = (inputtext) =>

let
Source = OData.Feed("https://api.datamarket.azure.com/data.ashx/amla/text-analytics/v1/GetSentiment?text="&inputtext),
ToTable = Record.ToTable(Source)

in
ToTable

in
Sentiment

 

Can anyone advise on the best way to do this?

Best wishes,

Martyn

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: How to disable an api call?

Hi Martyn,

this sounds like a request for incremental load - which is not natively implemented, but there are a couple of workarounds for it.

Principle here would be to store the data that you've already retrieved either in a "non-refreshable"-query in the query-editor (http://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/) or in an Excel- or csv-file (http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/).

 

You then merge this with your master-table containg all "Review"-values on Left- or Right-Anti in order to return only those values who haven't been passed to the API yet. Then append both tables to get all results in one again.

 

Or are you looking for sth different?

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: How to disable an api call?

Probably an @ImkeF type question, but I would imagine that you would need to store your scores in something accessible by M, pull that into your query, merge/join your data sources to filter out those already scored and call your API then use the result of that plus your original scored data, append those two together and feed that to the data model.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User III
Super User III

Re: How to disable an api call?

Hi Martyn,

I'm not familiar with these sorts of calls, so one question here:

The values that should possibly be excluded: Where do they come from? Is this some sort of recursive operation here?

Do the values come from one result of the api-call itself - & shall then be checked against all values that have been called so far?

Or is it "just" the list/table that feeds into the function itself? (Then we would only need to remove duplicates)

 

Also could you please give the full code you have so far, for how this function is called?  

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




martynhale Frequent Visitor
Frequent Visitor

Re: How to disable an api call?

Hi Imke,

 

Many thanks for getting in touch.

 

I have a table that contains a field called 'Reviews' - this field contains text received from a customer survey. What I'm doing is to call the Azure Sentiment API pushing the 'Review' text via the api - I then get a sentiment score back from the api which I store in another field in the same table called 'Sentiment Score'. I have created a function for the purpose of calling the api. In simple terms that's it - it works really well.

 

However, my Azure account allows me 10K api calls a month for free - after that I have to pay, so I don't want to use up free api calls to re-score 'Reviews' that have already been scored. 

 

So I'm trying to find a way whereby I only submit new records to the api, new records being those that haven't got a sentiment score, rather than submitting all of the records in the table when there may only have a handful of new records.

 

I hope this makes things a bit clearer?

 

Best wishes,

Martyn

 

 

 

When I get a new 'Review' in the database I have to call the api again to get a score for it. 

 

 

 

Super User III
Super User III

Re: How to disable an api call?

Hi Martyn,

this sounds like a request for incremental load - which is not natively implemented, but there are a couple of workarounds for it.

Principle here would be to store the data that you've already retrieved either in a "non-refreshable"-query in the query-editor (http://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/) or in an Excel- or csv-file (http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/).

 

You then merge this with your master-table containg all "Review"-values on Left- or Right-Anti in order to return only those values who haven't been passed to the API yet. Then append both tables to get all results in one again.

 

Or are you looking for sth different?

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User III
Super User III

Re: How to disable an api call?

btw: You can also use Powershell for the export (there you could even schedule the execution) - all hacky of course...

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
martynhale Frequent Visitor
Frequent Visitor

Re: How to disable an api call?

Many thanks for your feedback, I'll give it a go.

Martyn

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors