Forum Discussion
Dataset too slow when appending query changes
Hi,
I imported a new dataset this morning, have performed certain queries with no errors. I now am trying to close and update these queries however this is taking far too long. It loaded around 630 KB almost instantly but in 30 minutes it has loaded only 20KB more. It will take over 11 hours for my request to finish at this rate.
No one is using the datasource currently
The app has been closed and opened
The computer has been restarted
The PC seems to be doing something as task manager is showing that >30% CPU is going to Power BI desktop as well as 1,150MB of memory
This is from a single excel file from a sharepoint site. There was no problem when intially loading it and the excel file is not corrupt and can be opened very easily and quickly in excel. It contains a large dataset (3000 rows + ) however my other sources are much larger.
I cannot do anything until these changes have been made, so this is an urgent problem!
Please any advice would be fantastic,
Kind regards,
Colleen
Hi Colleen ,
I got your point, several soggestions for your reference:
1. I believe R script can resolve this by writting the queries in R rather than M.
2. Alternatively, kindly refer this artical to use Table.Add Key :https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/
3. If you can, I suggest you update your RAM too. (most effective)
4. Open Power BI --->File--->Options and Settings--->Options--->Data Load---> Uncheck autodetect new relationships after data is loaded, uncheck the refresh option if the table is not needed in report:
5 Replies
Hi there,
Please adjust your connection to a local excel file to see if your excel file is the issue.
Otherwise you'll have to take a closer look at sharepoint,...
Thanks for your reply RobbeVL ,
What do you mean by adjust the connection sorry?
When initially setting the connection up there was no difference to all the other working connections. I did however notice PowerBI Desktop was running slow before this (on a very capable laptop) so I'm wondering if there's an internal factor at play within PowerBI itself.
Power Query is very aggressive about reloading the query preview. It seems that each additional step can result in re-reading the source and re-applying intermediate steps. If you have expensive query steps or a large data source, this slowdown will happen.
There were two steps that I used to speed up the query editor process:
1) Unchecked the "Allow data preview to download in the background." Make sure that this is unchecked in the report view and query editor.
2) The other option that I unchecked was "Enable refresh of this query." This is located in the Query Editor under Properties. DO NOT UNCHECK "Enable load to report."....you will lose the report.
