Forum Discussion
Execute Table.Combine between tables
- 8 years ago
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
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/
- ssaezgarcia8 years agoFrequent Visitor
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