Incremental Refresh using REST JSON API

[Anonymous]

Hi,

I have an API data feed which outputs new data frequently. I wish to import this data into Power BI by essentially appending the new data onto the data that has already been previously loaded into the model as this would result in a table containing all historical data provided by the feed. 

However, the number of rows in this data table will eventually reach many thousands and I, therefore, wish to avoid reloading/(requesting from the API) every row with every refresh if possible and would rather only request the new data that is not already present in my Power BI model. 

After doing some reading it seems like incremental refreshing using power query would meet my needs. However, my data is not coming from a SQL database but rather an API connection and I am not sure how to make it work?

Any advice would be greatly appreciated.

Many thanks,
James.

[Anonymous]

Hi @Anonymous 

From the blog provided by @GilbertQ , you can see an article about "Incremental Refresh using API" . You can refer to it .

Power BI Incremental Refresh with Web API Data Source | Data and Analytics with Dustin Ryan (sqldusty.com)

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi @Anonymous 

here is an example of my blog post where I show how I do incremental refreshing from a CSV File, the same can happen with the API

How you can incrementally refresh any Power BI data source (This example is a CSV File) - FourMoo | Power BI | Data Analytics

[Anonymous]

Thank you and @Anonymous for your replies.

I have read the articles you have linked. However, I am a little bit confused about the role of the SQL databases in all of this. 

Currently, I am connecting to the API and getting data by using a blank query and have not set up a SQL database at all. The date/time field I am using is returned by the API along with the results it produces which have an output frequency of about once per minute.

The API has the functionality for only the most recent result, all results between two dates or for all results over all time to be returned depending on what parameters are sent in the request.

For example, let's say that my API outputs the following sequence where each row is a separate output from the API at a different time instance:

Execution Date: 01/09/2022, Output: 1
Execution Date: 02/09/2022, Output: 2
Execution Date: 03/09/2022, Output: 3
Execution Date: 04/09/2022, Output: 4
Execution Date: 05/09/2022, Output: 5
Execution Date: 06/09/2022, Output: 6
.
.
.

I wish to only load the most recent result with each refresh rather than all of the results for all time.

Is it the case that I will have to use a SQL database to make my query foldable?

For context this is what my data inported from the API looks like where I have limited the results to the one most recently outputted. 

Kind regards,
James.