Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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...
Need Help
danielpcamara's avatar
danielpcamara
Resolver I
7 years ago

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.

sxt173's avatar
sxt173
Advocate II
7 years ago

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.

  • danielpcamara's avatar
    danielpcamara
    Resolver I
    7 years ago

    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-not-window-scheduled-task

     

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