Your tips on storing Power Query tables

[Millhouse]

Hi all,

I like Power Query a lot for connecting and cleaning data that I pull from an API - using the web connector. But whenever the api calls need to happen incrementally it gets complicated. Say I want to pull data for the previous week from the API and repeat this every week, how do I store all these snapshots of the data? Power BI will overwrite the data each week.

I've once used Power Automate to save a csv on Sharepoint every week, but I'd like to move away from storing data this way.

Some of you may suggest SQL for storage, but that probably means I have to move away from Power Query and make the api call in another environment too.

I read about some of the Fabric services like dataflow, pipeline, data factory, warehouse, lakehouse etc. but at the moment I don't have access to Fabric.

Is there a nice way to store incrementally refreshed data tables from Power Query without getting Fabric?

Any tips are welcome, 

Thanks

[GilbertQ · Accepted solution]

Hi @Millhouse 

I would honestly look at fabric because this will solve your exact issue you are having. With fabric you can use dataflow gen2 and simply say I want to append data which will keep appending the new data every time the job runs. With Fabric you can also have it pause and start so you only paying for the time when it is being used.