Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |