Store historic API data and append the new data daily

[HR_BI]

Hello PBI community,

I am looking for ways to store historical API data in the dataflow and would like to append all of the data to it that I get daily. The API I am accessing gives me the current data without any dates. An example below of how it looks like

ID Name  OS
1   A        OS1

2   B         OS2

3   C         OS3

The next day I could get the exact same records or have missing records or have a new record. I'd like to append whatever I get to the above. So now I would like to store this data for everyday, in order to show trends for my reporting.

I tried implementing incremental refresh. I created a custom column for the table in the dataflow named RefreshDate.

ID Name  OS    RefreshDate
1   A        OS1    yesterday's DTimestamp

2   B         OS2   yesterday's DTimestamp

3   C         OS3   yesterday's DTimestamp

I set up RangeStart and RangeEnd as well. Now whenever I refresh I'd like it to append today's data to the yesterdays data in the table. An example of how it should look like if we have the same exact data for today - 

ID Name  OS    RefreshDate
1   A        OS1    yesterday's DTimestamp

2   B         OS2   yesterday's DTimestamp

3   C         OS3   yesterday's DTimestamp

1   A        OS1    todays DTimestamp

2   B         OS2  todays DTimestamp

3   C         OS3  todays DTimestamp

Currently it wipes out the previous days data and stores the new one.

Could you please help with this solution. Any help would be greatly appreciated. 

[AlexisOlson]

Power BI / Power Query is not an appropriate tool for storing historical data. It's only designed to load data that is safely stored somewhere else.

See this previous thread for further discussion and links to other similar questions:
Update CSV/Excel instead of overwrite