Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
martynhale
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

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?

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?  

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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. 

 

 

 

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?

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Martyn

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.