How to not overwrite data in Power BI Dataflow

[JuliMed]

Hello!

I am facing the following issue:

I have 3 dataflows that are refreshed at diferent times due to a matter of resources. These 3 are linked and merged in a different dataflow that is used to get daily data and used in a dashboard (we store the "snapshoot date" as the actual date/time at the moment of the refresh). Until here all good.
Now what we are trying to do is to get one table inside the dataflow (or in another one, whatever is better) that gets this data and accumulates it for X weeks. I have seen the incremental refresh and for sure it will be useful but the problem is that I do not know how to accumulate data in a separate table as it gets overwritten when there is an update as the Source is the Daily one.

I've checked this thread → https://visualbi.com/blogs/microsoft/powerbi/historical-data-preservation-using-power-bi-dataflow/

But in this specific case the "Historical" data needs to be built with the daily one.

Thank you in advanced!

[lbendlin]

What prevents you from implementing incremental refresh against the original data source?

[JuliMed]

Hello!

Apologies fir the late response. Actually what happens is that the original datasource is a connection through a REST API that will return the last 4 months of data.
What happens is that we get this data through 3 different dataflows (one for each Region because if not is too heavy to get the data and it is closed due to a timeout).

Basically the requisite is that, as this data is continuously changing, it is needed to aggregate all the snapshoots we do for 2 weeks. As an example, yesterday I loaded data and I save yesterday's date in a  column (02/14/2022). Today I will load new data and I need to append it after what I got yesterday but with today's date (02/15/2022).
My problem is that I am not being able to append this data as when the append occurs it overwrittes whatever was already there because the "source" table does not have yesterday's information anymore, only today's one.

[lbendlin]

My question still stands. What prevents you from implementing incremental refresh against the original data source?

[JuliMed]

What prevents me is that, as data comes from a query to an API, the original data source is overwritten with new data on every load and in order to use an incremental refresh, "old" data needs to persist.
The data that I had yesterday, today is erased and changed with today's data. 

[lbendlin]

You may be better off using event based reporting. Are your data changes tracked in your source system?