Following are the STEPS ;
We take advantage of Protection Structure in Excel. This is where the magic begins, follow carefully:
1. Stay in the Excel File and go to Review Tab
Note: (Excel and not Power Query)
2. Choose Protect Workbook
3. Ensure the Structure box is ticked
4. Provide a Password (optional)
5. Confirm by re-entering ( if password is supplied)
This not only restricts access to queries but also stop any further way to get into the editor. The query tab in the Ribbon is greyed out and you so is the option to edit under Get Data. Below are two images showing the same.
NOTE
Locking the workbook structure also locks the ability to slip into Power Pivot.
Another obstacle is that your structure of the workbook is locked, meaning you can not add, delete, or make any further structural changes to your file.
Does doing a Refresh work when you Protect Power Queries?
This is another set back!!
In case you use Power Pivot the answer sadly is no, else refresh works completely alright. Furthermore, when all your tables are on worksheets or as connections, the refresh works just fine.
If you have a single Power Query that lands in the data model, you are jammed. If Power Pivot is involved and you have protected Queries using the above method, then refresh fails.
Is there another trick ?
Yes certainly, but not very effective.
Firstly carry out the steps like you normally do or wish to in Power Query.
Once you click OK, load the data as a table onto a sheet.
Click anywhere within that table (to stay within the table)
And then create a new Table by Click on: Data > From Table.
What outcome is observed from the above steps and
Why is there a need to create another table?
The above steps enable us to makes a new query based on the initial query you made.
The difference is that in the new Table, there is only one step, which is the “Source” step.
Any updates to the original query will reflect on the second (New) one when you press “Refresh”.
And there you get a table not protected but your queries aren’t visible in the sheet anymore.