Forum Discussion
Slow refresh with small excel as source
To be honest, on first sight I don't see any code that would directly explain the slow refresh.
10mb doesn't sound as a small Excel to me though. What are the numbers of records?
I assume that the final result is what you require?
So in fact "Hechos" is not 55x the number of records from "Maestro", but about 3x.
This is what I would try:
1. Use Table,Buffer for Source and each of your 4 tables.
Source = Table.Buffer(Table.SelectColumns(Maestro,{"Clau", "Estat", "Data Creat", "Data Actualitzat", "Data Entrega"})),
CreadasFinal = Table.Buffer(#"Creadas - Added Custom1"),
Likewise for the other tables.
OR:
2. Create separate queries for each of your 4 tables and an additional query to combine these.
Hope this helps.
I have to say that the "Maestro" table stops around 10mb, but "Hechos" table stops around 550mb.
I'm using "Maestro" as source for "Hechos", with this source, I build 4 different tables that I combine at the end. I do this because "Maestro" has one 'Estat' (state) and 3 different dates 'Data Creat' (Start date), 'Data Entrega' (End date) and 'Data Actualitzat' (Update date) and I want to be able to compare this dates as only one dimension.
Here is the Advanced Editor code for "Hechos":
let
Source = Table.SelectColumns(Maestro,{"Clau", "Estat", "Data Creat", "Data Actualitzat", "Data Entrega"}),
#"Creadas - Removed Other Columns" = Table.SelectColumns(Source,{"Clau", "Data Creat"}),
#"Creadas - Added Custom" = Table.AddColumn(#"Creadas - Removed Other Columns", "Estat", each "Creat"),
#"Creadas - Added Custom1" = Table.AddColumn(#"Creadas - Added Custom", "Data Històric", each [Data Creat]),
CreadasFinal = #"Creadas - Added Custom1",
#"Entregadas - Removed Other Columns" = Table.SelectColumns(Source,{"Clau", "Estat", "Data Creat", "Data Actualitzat", "Data Entrega"}),
#"Entregadas - Filtered Rows" = Table.SelectRows(#"Entregadas - Removed Other Columns", each ([Estat] = "Aprovada" or [Estat] = "Entregada")),
#"Entregadas - Added Conditional Column" = Table.AddColumn(#"Entregadas - Filtered Rows", "Data Històric", each if [Data Entrega] = null then [Data Actualitzat] else if [Data Creat] > [Data Entrega] then [Data Creat] else [Data Entrega] ),
#"Entregadas - Removed Columns" = Table.RemoveColumns(#"Entregadas - Added Conditional Column",{"Estat", "Data Actualitzat", "Data Entrega"}),
#"Entregadas - Added Custom" = Table.AddColumn(#"Entregadas - Removed Columns", "Estat", each "Entregada"),
EntregadasFinal = #"Entregadas - Added Custom",
#"No Entregadas - Removed Other Columns" = Table.SelectColumns(Source,{"Clau", "Estat", "Data Creat", "Data Entrega"}),
#"No Entregadas - Filtered Rows" = Table.SelectRows(#"No Entregadas - Removed Other Columns", each ([Estat] <> "Aprovada" and [Estat] <> "Entregada")),
#"No Entregadas - Renamed Columns" = Table.RenameColumns(#"No Entregadas - Filtered Rows",{{"Data Entrega", "Data Històric"}}),
#"No Entregadas - Removed Columns" = Table.RemoveColumns(#"No Entregadas - Renamed Columns",{"Estat"}),
#"No Entregadas - Added Custom" = Table.AddColumn(#"No Entregadas - Removed Columns", "Estat", each "Entregada"),
NoEntregadasFinal = #"No Entregadas - Added Custom",
#"Otros Estados - Removed Other Columns" = Table.SelectColumns(Source,{"Clau", "Estat", "Data Creat", "Data Actualitzat"}),
#"Otros Estados - Reordered Columns" = Table.ReorderColumns(#"Otros Estados - Removed Other Columns",{"Clau", "Data Actualitzat", "Estat"}),
#"Otros Estados - Renamed Columns" = Table.RenameColumns(#"Otros Estados - Reordered Columns",{{"Data Actualitzat", "Data Històric"}}),
#"Otros Estados - Filtered Rows" = Table.SelectRows(#"Otros Estados - Renamed Columns", each ([Estat] <> "Aprovada" and [Estat] <> "Entregada")),
OtrosEstadosFinal = #"Otros Estados - Filtered Rows",
Hechos = Table.Combine({CreadasFinal, EntregadasFinal, NoEntregadasFinal, OtrosEstadosFinal}),
#"Hechos - Changed Type" = Table.TransformColumnTypes(Hechos ,{{"Data Històric", type date}})
in
#"Hechos - Changed Type"
Thanks.
To be honest, on first sight I don't see any code that would directly explain the slow refresh.
10mb doesn't sound as a small Excel to me though. What are the numbers of records?
I assume that the final result is what you require?
So in fact "Hechos" is not 55x the number of records from "Maestro", but about 3x.
This is what I would try:
1. Use Table,Buffer for Source and each of your 4 tables.
Source = Table.Buffer(Table.SelectColumns(Maestro,{"Clau", "Estat", "Data Creat", "Data Actualitzat", "Data Entrega"})),
CreadasFinal = Table.Buffer(#"Creadas - Added Custom1"),
Likewise for the other tables.
OR:
2. Create separate queries for each of your 4 tables and an additional query to combine these.
Hope this helps.
Thanks!!
The Table.Buffer solved my problem, I used it in "Maestro" too and it works fine.