Forum Discussion

ssaezgarcia's avatar
ssaezgarcia
Frequent Visitor
8 years ago
Solved

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 t...
  • ssaezgarcia's avatar
    ssaezgarcia
    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