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.

[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.☹️