Execute Table.Combine between tables

[ssaezgarcia]

Hello everyone, sorry my bad english, I will do my best effort.

I have 2 tables in PowerQuery, first table have historical data and the second table have the newest data (same structure in both tables).

In the historical table when I refresh data Im doing an append step for import the second table (newest data), here is my code:

let
   

   Source = Oracle.Database("ellrep", [HierarchicalNavigation=true, Query="SELECT ...."]),

   #"Appended Query" = Table.Combine({Source, Newest_Data}),

   #"Removed Duplicates" = Table.Distinct(#"Appended Query", {"EVENT_NUMBER_EVENT_ITEM_NO"})
in
    #"Removed Duplicates"

Maybe is very easy to resolve my problem, Here is: when I refresh the second table (newest data) I want to append in the first table (historical data) without refresh historical data only append newest data. something like that:

Source2 = Historical_Data,
#"Appended Query" = Table.Combine({Source2, Newest_Data})

Please, if something can help me.

Thanks to all.

Sebastian

[ImkeF]

This sounds like incremental load. You can do this with the Power BI Premium version now.

If you don't have premium, you can try some workarounds, but they are not easy to implement and don't work for all cases. See here for example: https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

[ssaezgarcia · Accepted solution]

Thanks ImkeF for you reply, 

Im developing a solution with an if statement for identify when I have to do an historical load or an append of the new data, and its work good.

So, I have 2 tables (the same in the first post), I manually refresh the newest data and manually refresh the historical data but here I have implemented this in Power Query M:

//In this point I get a var from a table in my Excel

#"Changed Type3" = Table.TransformColumnTypes(Parametro,{{"Nombre_Parametro", type text}, {"Valor_Parametro", Int64.Type}}),
Habilitar_Carga = Number.ToText(#"Changed Type3"{2}[Valor_Parametro]),

//if the var is 1 I will load historical data, if the var is 0 I will append the newest data

Tabla = if Habilitar_Carga <> 1 then CargarHistorico() else RealizarAppend()

The functions are:

//Function for append new data

RealizarAppend = () =>
let
#"Appended Query" = Table.Combine({Planes_Accion, Planes_Accion_Nuevo}),
#"Removed Duplicates" = Table.Distinct(#"Appended Query", {"EVENT_NUMBER_EVENT_ITEM_NO"})
in
#"Removed Duplicates",

//Function for to load historical data

CargarHistorico = () =>
let
Source = Oracle.Database("ellrep", [HierarchicalNavigation=true, Query="SELECT ...."]),
#"Added Custom" = Table.AddColumn(Source, "EVENT_NUMBER_EVENT_ITEM_NO", each [EVENT_NUMBER]&[EVENT_ITEM_NO]),
#"Appended Query" = Table.Combine({#"Added Custom", Planes_Accion_Nuevo}),
#"Removed Duplicates" = Table.Distinct(#"Appended Query", {"EVENT_NUMBER_EVENT_ITEM_NO"})
in
#"Removed Duplicates",

The good thing about this solution is that do not duplicate the data as in the solution of Union in DAX.

I hope this can help other people.

Thanks, Sebastian