Forum Discussion
How to export data from one data table to other data table on the first of every month?
Hello,
I have a forecast table which is linked to an online source
| Month | Sales |
| Dec-16 | 1 |
| Jan-17 | 2 |
| Feb-17 | 3 |
Here the value under 'Sales' keeps on changing for the same month over the period of time. How do i take a snapshot of this table and save it to another data table on every first of the month so that i can have a comparison with the current value and the value which was predicted then?
2 Replies
You can create a reference of the table in Query Editor.
Open Edit Queries. Right click the table and click on Reference. It will create a new reference table.
Right click the reference table and disable Include in "Report Refresh"
I'm afraid, but PowerBI isn't designed to store snapshots of data that change over time. The data in it will always be connected to a source and once you refresh your report, everything will be overwritten.
In theory, there is one mechanism, that could do the trick (create one new query per load (!) -> disable their load -> append them all together), but apart from it being very clumsy and not practical for many use cases, it actually doesn't work when you perform the append ( http://community.powerbi.com/t5/Desktop/Disabling-quot-Include-in-Report-Refresh-quot-Bug-or-feature/m-p/89254/highlight/true#M37505 ).
Some workarounds for this scenario:
1) Use an R-query to export the current data into csv (in an append-modus). Therefore you should add a date-column when you import your data, so that the resulting table will always show which data belongs to which date. See here: http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/
2) Self-referencing hack: http://www.thebiccountant.com/2016/11/15/incremental-load-powerbi/
You should check the ideas-section if there is an idea for your specific use case which you can vote for!
