One click refresh of Power Query based Pivot Tables - Monkey Shorts Episode 002

Oooh, this has bothered me for years! Thanks!

That was great, Ken Puls

But i have some issues:-
In your Book, Master Your data with Power Query in Excel & BI, at Page No. 126, it is written that:-

Remember, if the query is loaded to the data model in either Excel or BI, a single refresh is sufficient to update both the data source and any Pivots or Visuals.

So i loaded all my queries in few of my Excel files (i am using Excel 2016) into data model, and now i am able to get Refreshed data in Pivot Table , without clicking Refresh twice.

But this led to my Excel files getting bulkier in Size. Now you are suggesting that one should uncheck "Background Refresh" and then there will not be any need to do double refresh.

What should i opt for?
(1) to load my queries in data model OR
(2) to disable background refresh, and not to load my queries in data model (and there was no other reason for me to upload my queries in data model)

Please let me know. This is very important for me.
Regards,

Well that’s a new one on me!

Thanks Ken! Great tip to solve an annoying problem. Thumbs up!!

Hi Ken, Were you meant to say “the power query chain is set up to refresh the power query first and pivot table second?”. I think you said the other way around which confused me a bit.
Can I just say I recently discovered your channel and bought your book and I am loving both at the moment. Thanks for sharing us your knowledge and making us better at using power query.

I'm trying this with VBA for more than 2 hours and i tested it out a lot of ways that didnt work. Thanks so much