Power Query in Excel - disable query from refreshing

Question

Hi.

I have an excel file that I'll be distributing to users outside the organisation. This file will be providing each business unit with information relating to them that will be input into their data.

I have static data that I have imported using Power Query from an Excel file on my network and have done a series of transformations on (connection only query). This query then feeds another query that filters this step for the logged in business unit and loads it into a table.

What I want to be able to do is, as this first query is static data and users are not going to need/have access to this original Excel file, disable the refresh on the static data query but still run the query that filters the data. Is this possible?

Thanks.

Answer 1

Thanks for that @Lz._ .

I'd considered going down the route of very-hidden worksheet but you can still reference these sheets in formulas despite them being hidden and therefore the data is easily accessible by just writing a formula to reference that sheet (there will be formulas referencing the tab that they will see so they'll know where the data is). I'm doing my best to lock this down whilst knowing that Excel isn't perfect for that.

I've come up with a round-about solution though still keen to know if anyone has any other solutions (okay if they do not). Given the static data, I ran my query and did the transformations required. I then created a new query using the 'enter data' functionality and copied the results from the other query into the 'enter data' input table (padding it with 20 rows of blank data as to not appear in the 'peek') and deleted the first query. This seems to solve my issue as the query data is all located inside the power query editor and it not trying to refresh outside. I made this connection only and then my filter query can filter this with no issues.

Answer 2

@pmc-086-8452 & Hi

As far I know disabling the Refresh on your 1st query isn't possible. As a workaround (not sure this is exactly what you want/need) I could suggest the following:

1/ Load to a new worksheet (instead of connection only) your first query that does the transformations
2/ Build your filtering query from the Table resulting of #1 and load it to a different worksheet
3/ Alt+F11 (this opens the VBA Editor)> In the upper left section of the window select the sheet where you loaded #1 > In the bottom left section select "2 - xlSheetVeryHidden" next to the Visible property:

Hope this makes sense & helps. Anything not clear to you please let me know. Nice day...

Answer 3

@pmc086 & Hi

I thought you wanted to keep the connection to the initial source in case you would have to share more up to date versions of the static data later... As this doesn't seem to be the case, breaking that connection and making a local copy of the data is more secure than what I suggested

Thanks for sharing your way around & for posting back
Nice day...

EDIT: If you feel the workaround I suggested can help others is a +/- scenario feel free to mark it as answer as well