Hi
I have a workbook (workbook 2) with approx 15 queries getting data from various excel and csv files. Nothing fancy, and the files are quite small (max 6.000 rows).
The problem is that a refresh all takes 10 minutes! When refreshing the queries one by one, I found that some were very slow, e.g. one query with 13 (thirteen) rows (same as source file and with no calculations) takes forever. I deleted the query and created it again - it took a very long time to load (connection only, add to data model), but I could refresh it afterwards without any problems. Then I created a relationship to my fact table - and again, the refresh took more than 5 minutes.
The strange thing is that I use the same lookup table and fact table in another workbook (workbook 1) with more or less identical queries - and in this workbook there is no performance problems.
I think I may have created workbook 2 by copying workbook 1 and then made some changes to queries etc.
Do you have ideas on what could be the problem and how to solve it?
Thanks
Marianne
Hi Marianne,
I wonder if something is corrupt in your second workbook as a result of copying it, as opposed to starting from scratch.
Are you able to re-create it from scratch to see if that fixes the problem? You can copy the query M code out from the advanced editor and paste it into a blank query so you don't have to do everything from scratch.
Let me know how that goes.
Mynda
Hi Mynda
Thanks for your reply. Actually, I have located the problem, although I have not yet figured out exactly what the problem is. It turned out that I have some calculated columns which were causing the slow refresh. When I deleted those, everything refreshed immediately.
As soon as I have a little time, I'll dig into it and try to figure out what I did wrong. I have a feeling that I somehow created a formula in those calcuated columns that caused some kind of "loop" - the refresh time seemed to increase each time I tried.
Thanks again for your help
BR
Marianne
