Disable refresh when working in PowerQuery

[Dmitry22]

Hi everybody.

I am using PowerQuery to combine data tables from several separeate Excel files. However, when trying to add some calculated columns into the resulting table, after each column is entered to the file, it start automatic refresh of all data in the model, and I have to wait until this is finische.d. AS result, adding of 15 colums takes more than an hour.

How can I disable any refresh when working in the Power Query editor? I will only need to see any figures after all colums are added.

[mdubdata]

I agree with VickyDev18 i have unchecked that box while using a Direct Query model and the painful pageloads slow down the report development quite a bit. 
I guess I am forced to put a TOP(100) in my SQL to handle this whiel building.

What do others do?

[canotuna]

this is one of the reasons i've recently started doing my powerquery workup in excel and for each large or slow data source i create a dummy table locally with a cut and paste copy of the data (my data sets are not enormous - of the 100s of thousands of rows) that i reference during development.

once i've taken things far enough to go back to live data i just cut and paste the contents of the original query advanced editor script into the query for the dummy data, rename it and continue.  often at this point i migrate the queries to powerbi.

i would love to hear if this is not the way but so far i find it works reliably.

[MikeIndeed]

This sounds like a good way to introduce transcription errors.  Copying and pasting large swathes of data can get you in trouble if you don't notice any accidental overwrites etc.

[pmanueco]

Hi @Dmitry22,
I encountered the same problem with a different data source, did you find a solution? I have a big database and it takes forever to do any change and its always refreshing. Also if I change from one page to the other whatever was loaded before it unloads.

Thanks in advanced, ill let you know if I find a solution.

[Dmitry22]

Hello. Unfortunately not really. I tried to implement proposed solution from this topic, but it does not change much. The only workaround seems to be to use a "dummy" data source with only a few entries at the time of coding and then replace it by "normal" once finished.

[foodd · Accepted solution]

One option would be to disable background data refresh to prevent changes from triggering an automatic refresh while you make changes.   

Follow this:  Disable Power Query background refresh

Steps:

In Power BI Desktop, you disable automatic background refresh by selecting File > Options and settings > Options, Current File > Data Load page > Background Data.   While editing:  Uncheck 'Allow data preview to download in the background'.  When done, return and Check this option to enable background refresh for the current file.

[VickyDev18]

This didn't work, at least not with Azure Data Lake Gen2 as the source. Every change triggers a refresh and then a painful wait.☹️