Limiting refreshes in Excel Power Query

Question

PROBLEM: I've a table which is refreshing when I don't need it to, and it's taking forever. In Power Query - I have several queries ganged together.

NewAddsStage reads in a directory with ~10 workbooks from a networked sharedrive, appends the data together, filters it down a lot, and loads to a table. It takes about 4 minutes to refresh.

NewAddsRoll uses NewAddsStage as a source, performs a several Table.Join's and rolls up the data, creating a couple columns which hold objects of type list. It forms a connection only.

Report1, Report2, etc use NewAddsRoll as their source, do different kinds of filtering, and load to tables (which are in the same workbook as the NewAddsStage table.)

Every time I go to edit the Reports, NewAddsStage refreshes, re-reading all its workbooks, and that takes way too long. I want them to just use whatever data is held in NewAddsRoll, without refreshing it or NewAddsStage. I've fooled around with Data > Connections > Properties for the connections, but it doesn't seem to help.

Do I need to put the NewAddsStage table into a separate workbook in order to stop it from refreshing? The underlying data changes, but less than monthly. I want to only have things refresh when I invoke the refresh manually. Is there a way?

FWIW, I'm running PQ on the desktop, in Excel 2013, under Windows 7, with the Formula.Firewall off.

Answer 1

Firstly, having your NewAddsStage table in another workbook isn't a bad idea if it's a very large set of data and you are only refreshing that data once a month. That aside, I feel like there is probably a "proper" way to do what you want, but I have found a bootleg way that seems to "work" even though it looks redundant.

Since you are already loading NewAddsStage into a table in your workbook, create a (connection only) Query from that table. Then have all your other queries run off of that as a base instead of NewAddsStage. This will give you access to the data in NewAddsStage without it being linked to all the workbooks and trying to refresh all the time.