Appending new data to Historical data

[Uspace87]

Hi all,

I would like to run a SQL query on a daily base with a "Today Date Stamp" and save it somewhere (Dataflow/Datamart..etc) appending everyday the new data. I was trying it with Incremental refresh but I am a bit confused.

Can someone suggest me the best solution?

Thank you.

[ibarrau · Accepted solution]

Hi. The best solution would be building the table in a storage. I mean data lake, data warehouse, database, etc. I wouldn't build an incremental storage at PowerBi. PowerBi is not a place to store data, yes it can incremental refresh and take data but it's not prepared for issues. For example, incremental refresh works everyday, but as soon as you need the smallest change on the dataset or dataflow (without a lake configured), you need to run a full refresh losing history.

Incremental refresh for PowerBi is meant for refreshing fast data from a storage that has a big history, I wouldn't build the history inside PowerBi.

The most close you can do would be a PowerBi dataflow connected to an Azure Data Lake gen2. That incremental could work because you can reach and see the files for any disaster or issue in order to modify or fix things. Using dataflow like that is like using dataflow as a DataFactory (moving data from a place to a data lake).

I hope that make sense.

[Ben1981]

Hi ibarrau,

This sounds like exactly what I need but I'm not sure how to go about setting this up. Would it be possible for you to through together some steps on how I can achieve this or if you know of any guides online and could point me in the direction?

Any help would be really appreicated.

Thanks