Store old data upon refresh?

Question

So I have a historical table that has data from years past, but then I have another table that has a live connection to download the latest daily data.

How can I structure my query to append the daily data to the historical table upon refresh so that the historical table will keep growing with each day's data when the model is refreshed daily?

Is this possible?

katiejroberts · Answer

&nbsp;Anonymous&nbsp;wrote:Yes, I know how to append the query to the histroical table, however, that is not my issue. Let me show an example:&nbsp;My historical dataset is a static excel file that has daily data from January 1, 2018 to March 21, 2018. My "live" dataset is connected to a webpage that updates daily. I can setup my historical dataset to append the live dataset to the bottom so that today's data (March 22) will then be included in the historical dataset. However, tomorrow (March 23) when I go to refresh my live dataset, the historical dataset will append the March 23 data, but the March 22 data will be gone. So now my dataset will have data from January 1, 2018 - March 21, 2018 and also March 23, 2018, but March 22 will not be present.&nbsp;Is there a way to append the daily data to a historical dataset and keep it there?&nbsp;Hi&nbsp;nobodyukno,&nbsp;&nbsp;Did you ever end up finding any sort of solution to this?&nbsp; I'm going through the same issue right now and not ready to give up just yet... haha.&nbsp; Please let me know - thanks very much.

v-xjiin-msft · Answer

Hi&nbsp;Anonymous,&nbsp;Basically, Power BI is an Interactive Data Visualization BI Tool. It is used to display data and store data.&nbsp;As you want to keep the history. I'm afraid your requirement cannot be achieved.&nbsp;Thanks,Xi Jin.

v-xjiin-msft · Answer

Hi&nbsp;Anonymous,&nbsp;=&gt; I have a historical table that has data from years past, but then I have another table that has a live connection to download the latest daily data.&nbsp;&nbsp;Can I understand as you have two datasets? One is the past data and one is daily data. If you want to combine the two queries into one. You can go to Edit Queries and try&nbsp;Append Queries option. Then publish this new report to Power BI Service, set&nbsp;scheduled refresh&nbsp;to update the daily data.&nbsp;Thanks,Xi Jin.

anonymous · Answer

Yes, I know how to append the query to the histroical table, however, that is not my issue. Let me show an example:

My historical dataset is a static excel file that has daily data from January 1, 2018 to March 21, 2018. My "live" dataset is connected to a webpage that updates daily. I can setup my historical dataset to append the live dataset to the bottom so that today's data (March 22) will then be included in the historical dataset. However, tomorrow (March 23) when I go to refresh my live dataset, the historical dataset will append the March 23 data, but the March 22 data will be gone. So now my dataset will have data from January 1, 2018 - March 21, 2018 and also March 23, 2018, but March 22 will not be present.

Is there a way to append the daily data to a historical dataset and keep it there?

danielpcamara · Answer

I had this question to.I finded an ugly solution, but works for smalls data sets and in cases that you don't need to refresh frequently.Basicly I:1 - use an Excel file to Query my dataset, and name the Query "New",2 - add a column "Updated in" with the formula "DateTime.LocalNow()"3 - Create a table "Store", in my workbook to store my dataset with the mase extra column (Updated in), this table is not a query and has no rows (yet).4 - Query my Own workbook to get the table Store (this Query will be only connection).5 - Then in the Query "New" I append the query "Store"&nbsp;All is set, now all I need to do is refresh my query "New", Manualy Copy the inteirer table, and paste into my Table Store (not the query, the real deal).If I need the history by month I need to Do this last step evry Month, as I say, is ugly but works.To make this more fast (sinze in my case I need to have the history of by day) I create an macro that refreshs my data, copy and paste, saves and close my workbook.The Table Store will be connected in my Power BI.

Forum Discussion

Store old data upon refresh?

8 Replies

Related content

Data Refresh Errors

Refresh data

Data refresh error

Data refresh failing due to account refresh limit (Pro)

Data Refresh Failed