Dataset too slow when appending query changes

[Colleen]

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

[RobbeVL]

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,...

[Colleen]

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.

[v-diye-msft]

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.

[Colleen]

Hi Dina @v-diye-msft ,

I have not been able to find the 'enable refresh of this query' setting however I have unchecked the other box you mentioned.

I fear there is a bug here. It's not that the data is loading slowly, every time it will load quickly up to a point (652 KB) and then stagnate with no progress (even when I left this for hours). My PC is working hard as the fans run quickly whilst this is happening with a large portion of CPU and memory allocated to Power BI whilst this happens (20-40% CPU, 1,500MB memory).

All this however there is no process happening.

The queries are on two tables from that datasource. Cleaning and then an outer join merge. Surely Power BI has the capability to do these functions? It's had no problem merging tables before and the stagnation makes me believe this is a bug.

See below, Power BI can stay like this for hours with no progress.

Please help as this is a pressing issue. Kind regards,

Colleen

[v-diye-msft · Accepted solution]

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-po...

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: