Power Query taking too much time to refresh

Hi All,
I have 8 excel workbooks each for a different year starting from 2014 till 2021. I update the latest 2021 data in the 2021 workbook and when I refresh the data, it takes a long time to update. In total there would be around a million rows as there is 8 years of data, but is there a workaround to optimize the Refresh time, so that only 2021 data refreshes and not all the previous years. The output is in excel pivot tables as I am comparing different years. In Power BI, we can disable the load for each query, but is there any method to minimize the refresh time in Power Query.
Melissa or anyone to the rescue ?

Hi @chris786,

Think there are a couple of things you could consider/examine:

  • Are there options to optimize the queries themselves? Look into Query Diagnostics.
  • Consider saving your historic data (before 2021) in CSV instead of XLSX
  • Can you off load some of the work to Dataflows?
  • Consider Incremental refresh, see article in post below if you are using Pro

I hope this is helpful

1 Like

Hi @chris786, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi Melissa,
I tried removing the unwanted columns from the data set, got a slightly better refresh rate with it.
However, I still need to try the csv route and will let you know the outcome.
I don’t have a pro BI license so will not be able to use the Dataflows method.
I guess 5 years of data will have a refresh impact…
Thank you.

I’m sure you have a Pro but not a Premium licence (these names are confusing…)
Please read through the linked article in that referenced post, dataflows could be a solution.

Thanks Melissa, unfortunately, I use the Power BI free version currently.
I read recently that the Power BI Pro has better data refreshes.

Hi @chris786,

Completing Melissa recommendation, I would suggest to have the historical data “merged” into a single CSV file. Usually having multiple files will be more time consuming rather opening one file.
You didn’t say how you work with that files - one query for each file or do you import them “from folder”.

And last but not least - depending on the transformation inside PQ, there are times when Microsoft recommend to “move” some parts outside of it - for example SQL server. Probably in your case this setup could add more complexity but you could use the free SQL version (SQL Server Express) on your desktop PC.

Kind Regards,
Lucian

1 Like

Hi Lucian,
Haven’t tried that approach, but sounds logical, I will try merging the historical data into one CSV and try it out.
Yes, I import all the 5 files from a folder, each file for a different year.
Will give it a try and let you’ll know.

Thanks.

Hi Melissa / Lucian,
I tried the approach of importing the historical data merged into one CSV format, and then appending the current year data (.xlsx format). The approach works and time taken to refresh has halved. I also checked the option for quick load in the query properties window. Don’t know if the quick load option also helped to it. Thank you both for suggestions.

2 Likes