cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nobodyukno
Helper V
Helper V

Store old data upon refresh?

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?

7 REPLIES 7
v-xjiin-msft
Solution Sage
Solution Sage

Hi @nobodyukno,

 

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

 

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 Append Queries option. Then publish this new report to Power BI Service, set scheduled refresh to update the daily data.

 

Thanks,
Xi Jin.

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?

 


@nobodyukno wrote:

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?


 

Hi nobodyukno, 

 Did you ever end up finding any sort of solution to this?  I'm going through the same issue right now and not ready to give up just yet... haha.  Please let me know - thanks very much.

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"

 

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.

@danielpcamara  but how are you automatically refreshing your data model in Excel? Your solution works in Excel if you manually refresh. But let's say you want this to happen automatically or you want to use the data in Power BI. Then you are stuck with whatever was last manually refreshed.

Yes and no.

uglier part comes now:
I create a Macro on VBA to refresh my workbook always that it is opened. something like this:

 

Private Sub Workbook_Open()

Sheets("Consulta").Select
Application.Goto Reference:="Atualizado" 'Name of Table to Query
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:="Atualizado" 'Name of Table to Query
Selection.Copy
Application.Goto Reference:="Histórico" 'Name of the Table to save
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

ShtSobre.Activate
ThisWorkbook.Save
Application.Quit

End Sub

To Reference, your Query will need to merge your history with the new Query, like this:

History:

let
    Fonte = Excel.CurrentWorkbook(){[Name="Histórico"]}[Content],
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Aplic", Int64.Type}, {"Data", type date}, {"Valor", type number}})
in
    #"Tipo Alterado"

All Updated:

let
    {your code}
    #"Consulta Acrescentada" = Table.Combine({ PREVIOUS STEP, Histórico}),
    #"Duplicatas Removidas" = Table.Distinct(#"Consulta Acrescentada", { YOUR PKs })
in
    #"Duplicatas Removidas"

And at last, You can config a Batch or config your OS to open the workbook at a specific time. Something like this:

https://stackoverflow.com/questions/32145876/can-batch-script-start-some-programs-at-specific-time-n...

 

Be aware this will not work for databases that has a lot of rows, because the Excel limit of rows.

Hi @nobodyukno,

 

Basically, Power BI is an Interactive Data Visualization BI Tool. It is used to display data and store data.

 

As you want to keep the history. I'm afraid your requirement cannot be achieved.

 

Thanks,
Xi Jin.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors