• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power query - cancel/avoid data model refresh when go back to Excel ?

L

Lolo

Member
Hello,

I have an excel file and I use power query to connect to an Oracle DB, and retrieve some data. The SQL request used takes some times (several minutes).

'Load To' option is set to 'connection only' + 'Add to datamodel' check box ticked, and option 'Fast data load' is also ticked.

Problem is that each time I make an update in power query ,and the go back to Excel, data model is refreshed, and I have to wait the end! Excel specifies (in the status bar) to press 'Escape' key to cancel the refresh, but it isn't working :(

I would just want to refresh the data model on demand, not each time I go back to excel ! :(

I'm probably not the only guy to have this, so do you know any trick or option to cancel / avoid data model refresh when go back to Excel (from power query)

Thank you for your help
 
Last edited:
Thank you,

already used before creating the post, but no effect on poweur query, when I go back to Excel...
Or I used it wrongly ? I have selected the action 'Disable Refresh', it has been taken into account, since when I right click I see that it propose to enable the refresh. But I go to PQ, make a modif, save, go back to Excel, and the data model refreshes...
 
Ok, so it's PowerQuery that you want to disable refresh. Not Data Model/PowerPivot.

I don't believe there is function for that.

Typically, during development stage, I limit number of rows by keeping only TOP N rows in query window to speed up things.
 
Oh and disable Fast Load. While data is loaded faster, Excel will be unresponsive during data load. See the warning sign beside the setting.
 
Thank you.

globally, I also limit the number of row in DEV stage.
But in this particular case, the SQL request use a ORACLE hint, that force a FULL scan of the db table, so event if I limit the number of rows, the request is long, So I need to remove also the oracle hint. Feasible of course, but i'm fed up to remove and add these info from the SQL request ;)

Checked also the last link, and already used these advices.
So I will continue as is. Not a major issue actually.

Thank you for your time.
 
You must log in or register to reply here.
Back
Top