How you protect power queries is a question that will come up after you've built a solution that relies heavily on Power Query, especially if you're going to release it to other users.
(This is a quick post, as I'm in Australia at the Unlock Excel conference, but still wanted to get a post out this week.)
Can you Protect Power Queries?
The answer to this is yes, you can. It’s actually very easy, and prevents your users from not only modifying your queries, but adding new queries to the workbook as well. Essentially, it shuts the door on any additions or modifications to query logic, while still allowing queries to be refreshed… at least, it should.
So how do we Protect Power Queries?
To protect Power Queries we simply need to take advantage of the Protect Workbook Structure settings:
- In Excel (not Power Query), go to the Review tab
- Choose Protect Workbook
- Ensure that Structure is checked
- Provide a password (optional)
- Confirm the password (if provided)
Once you’ve done this, the Power Query toolsets will be greyed out, and there is no way for the user to get into the editor.
Does refresh work when you Protect Power Queries?
This part kills me. Seriously.
The answer to this question depends on whether or not you use Power Pivot. If you don't, then yes, you're good to go. As long as all your tables land on worksheets or as connections, then a refresh will work even when you protect Power Queries via the Protect Workbook method.
If, however, you have a single Power Query that lands in the data model, you're stuffed. If Power Pivot is involved, then the refresh seems to silently fail when you protect Power Queries using this method (and I don't know of another short of employing VBA, which is a non-starter for a lot of people).
It's my feeling that this is a bug, and I've sent it off to Microsoft, hoping that they agree and will fix it. We need a method to protect both Power Query and Power Pivot solutions, and this would do it, as long as the refresh will consistently work.
Caveats about locking your workbook structure:
Some caveats that are pretty standard with protection:
- Losing your password can be detrimental to your solution long-term. Make sure you have some kind of independent system to log your passwords so this doesn’t happen to you. And if your team is doing this, make sure you audit them so you don’t get locked out when as staff member leaves for any reason.
- Be aware that locking the workbook structure also locks the ability for users to get into Power Pivot.
- Workbook security is hackable with brute force macro code available on the internet for free. (Please don’t bother emailing me asking for copies or links to this code. I don’t help in disseminating code which can be used to hack security.) While protecting the workbook structure will stop the majority of users from accessing your queries, it should not be mistaken for perfect security.
22 thoughts on “Protect Power Queries”
Does workbook security differ from the regular protect file/sheet? I thought Microsoft fixed this in Excel 2013 (they switched to a more secure algorithm so the standard binary hack didn't work).
So excited by the opening "yes you can", followed by killing me with the "but"
Sorry about that Wyn. For reference, I got an agreement from someone that it sounds like a bug... Still waiting on an answer from the Dev team on it though.
Phil, there are four kinds of security in Excel; Workbook, Workbook Structure, Worksheet and VBA project.
While there have been improvements made, each is still vulnerable to hacking if the user is bound and determined to get in.
Pingback: Power BI Premium, Apps and Report Server | Guy in a Cube
It should also be noted that the queries can still be displayed by using the "Send Feedback" button (at least in Excel 2013) which opens an email document with all of the query code in the workbook exposed.
Interesting... the Send Feeback buttons have changed in non-insider builds of Office 2016, so you don't see the email. Again though, one should not assume that you won't be able to see the queries, only that a novice user wouldn't be able to edit them.
Hi ken,
Excitement, disappointment and now excitement again! It works for me when using Power Update.
For PU a query must be loaded into Power Pivot for updating the data to SQL server. So, I have Power Query's to refresh and a datamodel in Power Pivot to refresh. When locking the workbook the password can be omitted in a PU task and during refreshing PU unlocks the file, does its job and locks the file again before closing it.
For me this seems to work fine and I can finally use some sort of protection. Do you agree or am I missing something?
@Mark, that's cool. I haven't tested it myself, but sounds like you uncovered something useful there. 🙂
Ken,
Nice article ! I was wondering can you lock power query in the Power BI workbook as well ? Havent found a way to do it.
Hi there. I've attempted to limit access to PowerQuery using the Protect Structure mechanism, but the query fails when returning the data to the worksheet (I can see that the query is in fact running, as it shows the output in the popup when I hover over my query). I'm not using PowerPivot. Why would this be the case?
Hi Abhi,
The pbix file isn't intended to be secured except by publishing it to Power BI. So no, and I doubt they'll ever give you a way to do so.
Was this capability added very recently in PQ? When I enable Protect Workbook it greys out the PQ options, but trying to refresh the queries from the Data ribbon just fails with a "Download did not complete." error. I've tried on stock 2016 and 2013 using PQ last updated about a year ago...
Hi Paul,
This post is from May, so it's not recent. In fact, I think it goes back a long way. And even in the Insider built of Excel 2016 today, it's still not working.
Keep in mind, this only happens if you load your power queries into the data model (power pivot). If you land them to tables, they can still refresh. Sadly, that pretty much makes the entire feature useless to me, as I load virtually everything into the data model.
Hi ?Ken, Good article, thanks. Do you have any advice on how to protect a power query App if you are loaded it to a external customers systems or making the App distributable? You mention VBA, or should I take another route, such as transferring some of the core logic to an add-in.
Many thanks
Hi Peter,
If you're copying a Power Query script to an external customer system, there's not much you can do. The only thing I can think of is to maybe build your own custom connector package, but I haven't actually doe that myself. not sure if it would work as a method to protect IP alone. If you can move your logic to an addin so that it runs the script locally then copies and pastes the data to the customer workbook, that could work, but they won't be able to update it without your addin.
Hi Ken,
I have noticed, even after locking the structure, someone can copy the code and paste into a new blank query in new workbook.
Is there a way to prevent someone to copy your code, incase if you want to do so?
Hi. I´m using Excel 2013 all queries are landing on worksheet or as connections (nothing is loaded to data model, no power pivot is usedl). But the updates are not not working when i use "Protect Workbook" method.
Should it work in Excel 2013 or later versioon should be used?
Hi Viljar,
The issue I described in the blog post has been resolved. Given that I tagged this as Excel 2016 when I wrote it, I'm assuming that it was fixed in then 2019 version, but I can absolutely assure you that it is fixed in Office 365.
Yes, the PowerQuery seems to work now even in Protected Workbook. BUT interesting thing I just noticed: Even when your workbook is protected and you can't see the PowerPivot or PowerQuery content, you can go to Queries & Connections, select a query with right mouse button, then select "Show the peek". This shows a few rows of PowerQuery data, unfiltered, possibly with sensitive data, even if the data in related PowerPivot is hidden from client tools.
Hi Pek,
Yes, that makes sense. The features are more about protecting the structure than the data.
I honestly wish we could turn off those peeks anyway. (Nothing worse than accidentally pausing on one and having it kick off a background refresh that takes forever.) Would be nice if turning it off would also be included in the protect workbook structure settings as well.
Hi Ken - this is a great solution and has certainly prevented people accessing the Power Query in the main file - however, I have found that if someone copies and pastes the table produced by my Power Query into a new worksheet they are still able to access the query. In my case, the data is sometimes confidential - so I have concerns about people copying and pasting without using "Paste Values".
Is there anything that can be done to prevent the copying and pasting of all the data with the Power Query carrying over?
Hi Tam,
Unfortunately, there's no truly reliable way to block this if someone is intent on grabbing your query data. The best I can give you would be to add a macro that disables the copy functionality while on that worksheet, and re-enables it when that worksheet/workbook is deactivated. Having said this, if someone opens the workbook with macros disabled, the code won't run, so then you get into a whole hassle of trying to force users to enable macros on that workbook. (There are a variety of strategies for this too, but they are definitely going to impact workflows.