Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abhirajkakani
Frequent Visitor

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

 

MonthSales
Dec-161
Jan-172
Feb-173

 

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 2
Thiyags
Helper II
Helper II

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... ).

 

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!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.