Forum Discussion
Execute Table.Combine between tables
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
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
2 Replies
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/
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
