There is no query in the file you provided, how can I find a reason for slow query when there is no query?

As mentioned, I have to see what your query does, the steps you applied.

Hi Catalin

I am sorry but I never said I have an issue regarding slow query. I guess I didnt made my self clear enough and I appologize for that.

Actual issue is I have my data stored in google sheets (and to know the kind of data I have you can always refer to sample sheets provided above), now when I get data in power BI via web link. From there onwards everything gets slow.

I have 10 workbooks with 150-200 sheets in each one of them. Though right now I have uploaded only one workbook and BI started to hang. Imagine what if I add all remaining 9 workbooks also.

I know PBI is capable of handeling huge data campared to which I my data is very small, even though PBI drastically slows down.

My guess is as PBI have no integration with Google sheets that could be a reason (I may be wrong, if I am wrong then please forgive me as I am just a beginner)

Thanks in advance

The only way to get data into PBI is through power Query. PBI has power query and power pivot as modelling tools. Unlike excel version, you cannot bring data directly in Power Pivot, so you have a query from a website. (google sheets in your case). If the refresh time is slow, then the query is slow.

The refresh time is strictly related to what your query does, and the measures you wrote. I hope you realize that the information you've provided is not enough to understand why the refresh takes a long time.

This is the beauty of the internet, almost 3 years later and this post is still solving problems, Thanks Mynda, you literally gave me days of report preparation with this tip.

I agree with you John. Thanks Mynda!

Hello, I am joining in on this threat, as I have painful issues with Power Query as well.

I am doing reporting in Excel, where I have a source file of 193 tabs with 10-30 rows and 4 columns in each. The Columns is as following:

[Question and answers] [month 1] [month 2] [ALL]

Each new file will contain the following month. 

In a second Excel file, I have made Power Queries for every single tab, where I manually have deleted the three top rows, the 3 bottom rows and promoted the headers. Nothing else.

The Power Queries are either placed above each other in a single tab, where I have done some index matching to the right in order to make graphs on them (Using an add-in called Thinkcell).

The structure of the file is too tough for my work PC to handle, meaning that it runs out of memory if I make the slightest changes, I have tried moving 100 of the Power Queries to a seperate file, but the problem still persists.

When I try to refresh all, I get the following error message " The following Data range failed to refresh: ExternalData_4". When I click continue it freezes for a bit and I get the error message again with a different External_data.

Furthermore, I get an error message, that ""This won't work because it would move cells in a table on your worksheet."". 

It does not fix the issue if I move the numeric data, the problem doesnt apply for every PQ, and furthermore there is a 2 row space between every PQ table.

Thats a bunch of problems, and I am hesitant to fix it, as I am afraid that I will end up spending a lot of work on a new file that will have the same issues again. Making the file in the first place took me approximately 40 hours, which is a shame that it is basically useless.

My coworker suggested me to a probable solution, where I could add columns to the different PQs in order to merge them together to one large PQ, and then make the seperate PQs connection only so I only have the merged one.

I cannot make changes to the PQs in the file as it makes Excel crashes, and I am hesistant to start all over, as I am unsure that it will be a fix to my problem when the PQs start accumilating.

Registered for the sole purpose of reviving this very old post and thanking Mynda (Post #4 here) for the "Background Data" solution.  Never would have expected this to create the logjam it did - removing that checkbox changed my large array of large PQ's from several minutes to a few seconds.  Thank you!

Hi Mynda,

As start the turning of the data load background refresh option helped to refresh it quicker, however once I reopen the file later it becomes very slow. my data only has 2 excel file for testing with 10000 row items with 10 columns. Appreciate your help as I am trying to solve this from 1 week.

Hi Kirtiraj,

Please start a new thread with specific information about your query. Please address it generally as I may not be the one to answer it.

Mynda