Appending data without replacing data

[cip]

I know that there are similar posts out there regarding this but I still can't wrap my head around it and would appreciate it if someone can offer help with this.

I have a PowerQuery table that extracts JSON data from a web API but everytime the table is refreshed, the latest data will overwrite the existing data.

I tried creating another table to append the new data to the historical e.g. PowerQuery table  --appending daily new data 01/02/2022 --> historical data table which contains the data when the table is first created, say 01/01/2022, but when the PowerBI report is refreshed the new data from PowerQuery will replace the previous data appended, meaning I now have 01/01/2022 and 01/03/2022 instead of 01/01, 01/02, 01/03/2022 data.

The data set itself contains timestamps but are not in the standard yyyymmdd format (if that matters), and I am not sure if incremental refresh is what I am looking for as I don't need to drop the data that goes past certain historical period/ roll forward the historical period. I have also read that PowerBI is not the right tool at all because it is not created for storing data so some guidance will definitely help if I need to look for something else.

Thanks a lot!

[cip]

Sorry for the radio silence, I have been AFK for the past two days.

I will try my luck with PowerAutomate then and for sure will bear that in mind next time I come across similar situations when I need to store data.

Thank you so much for the swift response and help, really appreciate it! 🙏

[AlexisOlson · Accepted solution]

Yes, Power BI is not the right tool for storing data. You could use a Power Automate process like this or any other process you choose to store the data, then point Power BI at wherever you saved that daily data.

See these prior threads too:
https://community.powerbi.com/t5/Desktop/How-to-append-data-over-time/m-p/1789673

https://community.powerbi.com/t5/Desktop/Make-time-series-of-daily-data/td-p/1769767

https://community.powerbi.com/t5/Power-Query/Update-CSV-Excel-instead-of-overwrite/m-p/2307705

[Daryl-Lynch-Bzy]

Hi @cip  - Your scenario suggests that you want to keep and import JSON data from previous days, but when you refresh your Power Query can only find the current JSON file.  In this situation, I would recommend using Power Automate to extract the JSON each day and then load the JSON into SharePoint library.  Power Query are then import all of the daily JSON files from the SharePoint library to combine them.