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