Slow refresh time

[Justas4478]

Hi, I have where my refresh is taking aproximately 10 min to refresh.
When I refresh it takes about 5 min from evaluating query to start pulling data from file, and the files are stored on my desktop and not remote network.
Majority of the files are small but there are two files that are 30+ MB and combined they have over 1 Mil 185 K rows.
Is such refresh times for files those size and amount of rows are normal?

Main data is combined from mearging two tables and only taking some of the columns from one of the tables up on mearge.

Thanks

[danextian]

Hi @Justas4478 

If we are talking about flat files like CSV or Excel given the number of rows, 10 minutes isnt that long but there are factors that come into play other than the file size. 

• the data, if excel, being in a single worksheet will refresh faster than the same number of rows but are distributed across multiple worksheets
• a file vs two or more with the same number of rows will refresh faster given the same transformations applied
• calculated columns and tables needed to be written in to memory so they add to the refresh time
• Power Query transformations applied

[Justas4478]

@danextian Unfortunatelly one excel file canot hold all rows thats why I had to split it in to two files.

[Bibiano_Geraldo · Accepted solution]

Hi @Justas4478 ,

A 10-minute refresh time for 30MB files with 1.18 million rows is not unusually long, but there are ways to speed it up. First, check the file format; CSV and Excel are slower than more efficient formats like Parquet. You can also optimize Power Query by removing unnecessary columns and steps early in the process. If you're merging large tables, try to minimize the data you're combining and avoid unnecessary transformations. Use Incremental Refresh if your dataset only changes partially (e.g., daily or monthly data), which can greatly reduce refresh time. Additionally, monitor your system’s resources (CPU and RAM) and close unnecessary applications during the refresh to ensure Power BI has enough memory to process the data quickly. Lastly, using the Performance Analyzer in Power BI can help pinpoint specific areas that are slowing down the refresh.

[Justas4478]

@Bibiano_Geraldo While refresh is runing my resources are at 80% so I have enought of them.
File types are xlsx and unfortunatelly I cant change them since this is how I get them.
The files only change one every few weeks, but that is ireallevant since up on change all old files at the moment are removed and only new ones are kept (This might change in future if requested).
I did try to remove as much as possible early steps.
I will try to see if I can remove some column.

[Bibiano_Geraldo]

Well done @Justas4478 , try to see if you got unused columns to remove them, but 10min for a size of your data, is expected due you have transformatios such merge tables, this kind of transformations take time to process depending on size of your tables.