Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rookarumba
Helper III
Helper III

Apply Query Changes : Taking too long , like hours or so

Hi Everyone,

 

My "Apply query changes' is taking far too long. This wasn't the case when I was working on the same dataset (2m rows) of data.

 

Recently I did some changes and removed some of the steps within the query that required Merged.

 

And now when I apply changes to it, is taking forever and seem like is stuck at loading a particular query and it wont move beyond it.

 

Additional Information

I import all my data into PowerBI via Excel and did all the query within PowerBI.

21 REPLIES 21
Anonymous
Not applicable

Any Update on this thread. Here my source is Sharepoint. Am facing the same issue for Prod reports. Here I went back to Query because I need to change the number of columns in Delimeter which was the change I applied.
Urgent. Need Help.
Many Thanks.

V-pazhen-msft
Community Support
Community Support

@Rookarumba 

Try the solution in this post: https://community.powerbi.com/t5/Desktop/quot-Apply-Query-Changes-quot-takes-long-time/td-p/247419

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

asalcedo29
Employee
Employee

Did this got resolved? I'm having a similar situation where I want to reduce the number of rows in a 4M row Dim table using a Fact table (to get only applicable cases). After loading the data, power query takes 2-3 hours to do an inner join!

 

Any advise appreciated. 

Anonymous
Not applicable

I am also having problems with the time taken to refresh my queries.

 

There are 14 queries in my report, and most of them run in a reasonable amount of time. On 4 of the queries I have used the List.RemoveLastN function, and as I go through each of these queries it is on this line of code that the query is taken so long to run.

 

Has anybody noticed whether it is specific functions that are causing there queries to run slowly.

kishorekvn
Frequent Visitor

Hi,

 

If Power BI is taking too long time to load file, follow below steps:

1. Open the excel or any other source of data and remove all empty cells apart from your data.

2. Open Power BI --->File--->Options and Settings--->Options--->Data Load---> Uncheck autodetect new relationships after data is loaded

 

These two steps helped me a lot to reduce time while loading data.

 

@Rookarumba 

Anonymous
Not applicable

This was happening with me, then I got a System.OutOfMemoryException... It finally opened after I opened the file in another instance of excel. The process was 4.5GB and yet the file I was trying to open is 8MB... MSFT I think your PowerQuery has some memory leaks.

laggerok19
Frequent Visitor

The same thing here. I confirm the bug exist as of 28-Sep-2018.

 

We have <10 non live data sources each extracting just ~20 rows from Tabular models my means of DAX. Extracts themselves happens in ~1 minutes in parallel, but union them into common dataset takes 12-15 minutes, while should be few seconds.

 

Do you know whether bug request was raised for this?

@laggerok19 , I'm not too sure if this is a bug but is more on how PowerQuery is refreshing the query when some changes are done on it.

As of today, I'm using a method suggested by Ken Puls (I can't remember what is the name now but i can search for it and post it here again) so the queries won't evaluate everytime.

 

Also, I've upgraded my RAMs to 32GB and that reduces the queries timing from hours to 15 mins Max.

 

If you can, I suggest you update your RAM too 🙂

 

 

@Rookarumba, this happens when you refresh data at the point of time when combined data set is refreshed. Data extracts are quick, but not union operarion. Im not even saying about data transformations.

We have 8 gb RAM to union ~120-150 rows. Completely not applicable performance for application that cost that money.

What is the way suggested? If that it to publish pbx to pbi reporting service and do scheduled refresh, its not our case. SLA mins are 10 min.

I have a report that basically I load few tables from excel file and accessing Windows Insight Usage from our account (Details and Summary) and take hours (6 or more) when a single change is happening. Example change the location of excel file using a new version of values. The application can switch from MSAccess Database from Excel.... In this case is worst. Failed.

 

Something almost at the end using Excel fail and I have to start over.... Any help will be very appreciated.

 

Oscar

@opereira, do you have a sample query which you can share?

maybe i can take a quick look and see if im able to help

@laggerok19 the RAM works for me 🙂

 

this is the other methods Table.Add Key

 

You can find the blog post by Chris Webb

Literally, you can see that the query didnt rerun for any of your merge functions (if you have a merge function)

 

https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-po...

t_phi
Regular Visitor

Hi everyone,

 

In my case, I have a very simple SQL query which should return around 60k rows of data. But it took hours for me to get over the "Apply query changes" page.

 

I'm connecting to a database via ODBC. I have tried to uncheck the "Auto Date/Time" in the Options > Data Load settings. But it doesn't help that much. Any advice on how to make it faster? Maybe some setup/configuration that I might have missed?

 

Thanks!

I'm in the same boat.

 

Trying to pull data out of Filemaker 16 Server via ODBC.  A refresh on 1 table of 50k rows takes 2-3 hours ... not acceptable for my client/solution.

 

Anyone have any suggestions?

Same thing here. I have a joined query that is taking toolong (>1hr) to retrrive 6.6M. 

Rookarumba
Helper III
Helper III

Hi Everyone,

 

My "Apply query changes' is taking far too long(more than 2 hours). This wasn't the case when I was working on the same dataset (2m rows) of data.

 

Recently I did some changes and removed some of the steps within the query that required Merged.

 

And now when I apply changes to it, is taking forever and seem like is stuck at loading a particular query and it wont move beyond it.

 

Is there anyway to improve the performance of my query?

 

Additional Information

I import all my data into PowerBI via Excel and did all the query within PowerBI.

Hi Rookarumba,

 

Query in power query is slower than database. It is not recommended to implement queries on big data frequently in power bi. As a workaround, you can do these operations in database.(e.g.: merge(join), append(union all)).

 

Regards,

Jimmy Tao

Unfortunately, i don't have the luxury of having a database since the files are data dump by another source.

 

Thus I'm using PQ to work on those data on a Quarterly basis.

 

The weird thing is, i work on a previous copy and it seem fine, just not sure why this copy doesn't work after i edited some of the M  codes

Anonymous
Not applicable

I am having a similar issues where I am attempting to load a query from an analysis services connection.  The report says it has loaded 970k rows which soulds reasonable, but it seems to be hung up in finishing the job.  I've been watching the load for about 45 minutes and am hesitate to walk away for fear that it will time out.  Once I get the initial load in I can apply some filters to better scale the data, but I just need to finalze the data connection.  My first instinct was to load everything then scale back, but I suppose the next attempt is to load limited data then ramp up.  Any other suggestions for a relative novice/non-programmer?

@Anonymous , 

 

my current workaround is rewriting the queries with R instead of M.

So instead of waiting for up to 2 hours for the queries to finish loading, R can finish the job within 15 mins.

 

Hope this help

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.