I have a tale with many receords that are retrieved form a API call. I then have to use one of the fields and do individual calls to the API to retrieve additional data for each record. I am running into a couple of issues. This results in an excessive amount of API calls - It very easily uses up the daily API quota.
I though if I had a tbale with all the data to a certain point I could then append the data with the records retrieved from the API, and then next time use the max date in the table as my new "From Date" parameter in the query to obtain the latest set of records from the API and append that to the table, This doesn't work as the previously appended data is lost when the query runs again and only the new data is appended. I would like the previous appended data to reamin in the static table and the new data to be added to it.
I implemented a manual workaround and exported all the records retireved to an Excel sheet which I link on OneDrive as my archived data. I then modified the query to only retrieve data from the last day I have archived data for. I then append the archived data and the new data into a single table for my report. This works for a coule of days until the new data gets too much again. So I constantly have to manually export amd add data to the Excell file and modify the "From Date" in my query.
I am looking for a way to automate this. Can Power BI retrieve data from the API and store it in a table or Excell file and then next time use the "Max date" or "Last Date" in that table as the "From date' for the query, and then update/append the table with the new data?
This is like an incremental refresh. I know BI has an incremental refresh option for the online version but that won't work as I need to be able to continue downloading the PBIX file, don't want to upgrade to get that functionality and a couple of other reasons.
