API/Web Service Calls for new records only

[Trebor84]

Hi, i have been experimenting with web services and have attached an example using Google Translate to translate a phrase for each row in a table.

In my job I need to use a web service to retrieve thousands of results in a table.  Is there any way I can do this on "new" records only to avoid having to repeat unnecessary api calls?  My data source is a SharePoint folder with a large number of Excel files which I combine into a table to run through a web service and new files are sent weekly. Is there any way I can maybe append to an existing table any new records that go through the api call and then remove those from the original table or maybe a completely different approach?

https://filebin.net/djhrkashvhg3qct5 

Thanks

[v-jingzhang]

Hi @Trebor84 

Power BI and Power Query is not a tool like databases that can store previous result for you. You would need to store the result somewhere else.

You can consider using R or Python to export Power Query result into some local files. 

Export data from Power Query to Local Machine or SQL Server using R scripts - RADACAD

Export data from Power BI to csv using Python – The BIccountant

How to Auto-Schedule Data Export From Power BI - sqlitybi

Or if you have Power Automate, you can also use Power Automate to create a flow to read Excel file, loop every row and call API to get data, then store the result to an existing file or a new file. Below are some video tutorials on Youtube for your reference. You will find more great tutorials there.  

How to use Excel in Power Automate Desktop - YouTube

How to loop through each row in an Excel file - Power Automate Desktop Excel actions tutorial - YouT...

Power Automate Desktop 101 - extract web data - YouTube

Microsoft Power Automate | Add data to Excel, get data from Excel, Conditions and Send Email | Guide...

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[mahoneypat]

Please also see if you can adapt the approach in this article to work in your scenario.

https://www.esbrina-ba.com/incrementally-load-from-sap-apis-or-almost-anything/?s=03 

Pat

[ImkeF]

Hi @Trebor84 ,
I would use dataflows to write to my own Azure blob store all new files on a daily basis: Configuring dataflow storage to use Azure Data Lake Gen 2 - Power BI | Microsoft Docs
Then just import new files in the dataflow and import the whole Azure blob folder to PBI.