Power BI Incremental Refresh Behavior with API Question

[bigmac025]

Hello,

I am calling an API from a vendor site that only publishes data on a daily interval that has been modified or added.  The initial data publish of the API (after setup) export the entire data set but after the initial run only publishes records that have been modified or added.  

I didn't know the API published data like this, so the first data refresh into Power BI went fine but when I updated it the next day all records in the Power BI table were wiped out except the records that were added or modified on the vendor side the day prior.

I researched and looked into PBI Incremental Refresh.  I followed this documentation closely: https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#configure-incre...

Even after implementing the incremental refresh in PBI, the behavior did not change and only modified or added records are refreshed in the PBI table and the previous day's data is not archived like the documentation states.  I am starting to wonder if using Incremental Refresh is not the right tool for this use case.  Is there another method I can use to preserve past data refreshes?  Is Incremental Refresh the right tool, but I possibly don't have something setup correctly?

Any advise would be appreciated.

Thanks in advanced,

Tim

[bigmac025]

Thank you @lbendlin !  That makes sense.  I now know what direction I will go.  I will connect to the API using an API  script I have and put the data in SQL Server.  This way the data will be backed up.  I will then connect Power BI to the SQL Server source.

[lbendlin · Accepted solution]

Remember that Incremental Refresh has two benefits. First is the speed-up when you only have process the new data, and second is that your data can grow beyond 10 GB thanks to the partition management.

The first benefit is ONLY realized when your data source query is foldable.  That is not the case with an API data source.

The second benefit is only realized when your partition boundaries are set by an immutable data source field  (ie "Date created"  rather than "Date modified".

You mentioned the archiving - neither Power BI Semantic Models nor Dataflows Gen1 support that.  You would only get that in Fabric with Dataflows Gen2  ("Append mode") and even then there is no safety net. You need to consider a scenario when your Power BI data is corrupted and you have to re-load everything from source.  If your source does not support that then you will need to create your own archiving solution , independently of Power BI.