Building a historic table from a daily dataset

[Ben1981]

Hi, 

I run a SQL query into a PBI report that has a daily snapshot of data but there is a requirement for historical data which our SQL server does not provide.

I'm pretty sure I've heard of people using PBI/PBI dataflows to create some sort of historical dataset where I could run my daily SQL query and store the results but I don't know how it's done. 

Is anyone aware of this or know how it's done, would be grateful if you explain the process or point in the right direction?

Thnaks

[lbendlin]

I'm pretty sure I've heard of people using PBI/PBI dataflows to create some sort of historical dataset where I could run my daily SQL query and store the results

Whoever says that is a snake oil salesperson.  Power BI has no memory, has no guarantee of storage, and should not be used for this.  Store your snapshots in a reliable system upstream, or better yet use a data source that is not snapshot based but can reconstruct any point in the past.

Incidentally the Delta Lake Parquet format may be able to accomplish something similar in the future. Delta Lake Time Travel | Delta Lake

[Ben1981 · Accepted solution]

Hi, I found the solution and it is possible to do by a bit of work around which basically involves you feeding a dataflow into itself. 

It's not ideal but when you don't have access to the right tools and need a job doing, you have to find a way.

Thanks,
Ben

[lbendlin]

Ignore my warning at your own peril. The moment you have to make even the smallest change to your dataflow your history will go poof!