Slow refresh with small excel as source

[deluisp]

Hi, I'm new using PowerBI.

I'm using two differents Excel files as source, their size are 136kb and 250kb. The problem is that it takes a lot of time to refresh.

I load from the files once, then i rename, remove, join and merge this tables to obtain other two tables as result. The first table has all the records from both files and the second one has four times the records of the first table with some different columns and values, I do this because I need to merge some time dimensions from the first table in one column.

Please, I would like to know why is this happening and how I can avoid that.

Thanks

[MarcelBeug]

It must be something in your queries. The table "Hecho" seem to be much larger than 4 times the records of the first table.

If you share the query codes from the advanced editor (or better: the Excel files with anonimized data - e.g. via OneDrive or  dropdox), we will be better able to help you.

[deluisp]

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.

[MarcelBeug · Accepted solution]

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. 

[deluisp]

Thanks!!

The Table.Buffer solved my problem, I used it in "Maestro" too and it works fine.