Be able to protect Power Query with password
Now any user can edit and change Power Query connections, it would be very interesting to be able to protect Power Queries with a password.
Hi Joana & others,
Thanks for logging this suggestion. Yes, being able to protect your queries would be quite useful indeed for delivering solutions. We’ll prioritize this based on the interest, so if you’re reading this and haven’t voted as yet, please do so.
Lead Program Manager
Now that I've written enough queries I started looking for this feature. Appears the problem still is that you can add some protection, but not refresh.
It would be really useful to be able to do this. I've only recently been asked for a password to unlock a spreadsheet so that an end user can modify it. I'm not happy about it, but I'm being pushing into a corner.
Having the ability to protect ones work would be great.
KISS should apply here, at least for first iteration. Make possible the refreshing of the data model whilst Power Query is hidden using the Workbook protection feature. This will allow for security to be built into the data model through simple M filters, rather than having to implement row-level, which slows queries and might not be appropriate in any case.
@Ed, I do agree, I am just doing this to give a end user a dataset they can't tinker with (or break more to the point), but they can refresh for updates, they are not altering or reusing the source, most would not have the skills. If it needed tweaking them I would have to tweak the view directly. (I did say only half measure)
That would be a pretty sad reason for MS not to do this. I mean, yes, Power BI is a wonderful tool and of course it provides subscription income to MS, but so does Excel under the Office subscription. Plus, Power BI and Excel are great complements to each other.
Delivering on this request will make the ecosystem as a whole more attractive to serious analysts and developments looking to develop analytical solutions using these tools. Because without being able to protect the work that they have developed is basically a deal breaker.
Anyhow, I haven't checked back in here fore quite a while, but I'm really pleased with the level of interest that there is on this. Plus the fact that is now being officially acknowledged by an Excel Team Admin as I can see above. I mean, that's fantastic isn't it?
Lets us all vote for this and hope that it gets implemented quickly.
I half wonder if there is no impetus at MS to do this because if you do this in Power BI, the query is automatically hidden from the end user, and they get a $10 Power BI license.
@regan - that would work but has serious limitations as it becomes difficult to quicky modify/tweak the query, and prevents it from being used in a MERGE or APPEND without breaking query folding. No subsequent tasks in the query would fold either.
If you have database access, as a half measure work around you can turn your query into a view and do a select * from.....
I look forward to being able to protect the query to enable a controlled published dataset.
We are all pretty much talking about restricting report data at the report level. We are using Excel and PowerQuery so that as IT people we can pretty much give the reports to the report owners who can then restrict them down as well as add pivots, charts, pretty pictures data from other sources or whatever. This is a nice, object oriented solution, the query parameters can be entered in a spreadsheet cell and locked down, The problem is a disgruntled employee can then go into the query, edit it and instead of running off with say the customer order book for his territory he can run off with the entire customer base. That's not nice, that stops us using it.
Auto Analyst commented
I can understand the reluctance to implement security. I'm guessing the target market for these applications are big businesses with security already implemented within their data structures.
However in SME businesses without the resources or skillsets to implement a professional quality, database-side security solution, I find much of my best work is restricted. Hence, I find myself having to either limit my reports to a very senior audience or make numerous static copies for general consumption.
An example being I've built a data warehouse in SQL server for which I need Excel's analysis capabilities for presentation (accounting).
I've got a workbook with a number of user-defined parameters to limit the amount of data loaded into Power Query from a SQL view. This works very well, but it would only take someone to key in a business unit parameter not under their remit, and have access to payroll data for example. So even having a workaround like a password table to filter the parameters wouldn't work, as the Power Query applied steps would be visible to the end users.
It's understandable but hugely frustrating for users at my level - we just can't wield the power of the software without an often unviable project to implement advanced security features to the source data.
Hi Ryan, the problem is that locking the workbook structure with password data can't be refreshed, also the Queries can be copied to another workbook.
Anyway, thank you very much for your interest in the subject
Ryan, that does work, but it also causes DAX to fail. So if your query is loaded to a table, that is fine,but if it feeds PowerPivot, it causes PowerPivot to stop working, or anything that relies on the Data Model, like the CUBE functions.
You can lock the ability for individuals to edit the applied steps in the power query by locking the Workbook Structure with a password. Let me know if that helps!
SALIOU NDOYE commented
I agree. My excel worksheet is linked to a global database and I am filtering via power query. It will be useful to protect those filters. And I hope the security will be more strong then excel vba editor.
I am so excited for the functionality of power query. I am able to easily retrieve data from a complex database create elaborate index and match functions and create a company wide resource for project management. However now that I have completed this amazing work of art I find out that I am unable to protect the data. With out security this amazinag feature of power query is limited to only the programmer and ultimately useless. Please create a solution to this problem.
Wyn Hopkins commented
I have numerous situations where clients want to be able to filter the data on import to restrict the data in the file. Without security it is then much more difficult to prevent someone turning that simple filter off. Basic security would be extremely useful.
My vote is also for an option to protect queries where user is able to change the link of source file and able to do all options in excel sheet.
I vote for it. One should have password protection option.
Yann Courtel commented
User anonymous misses the point. Some Excel files go to external users and it would normal to not want to expose your internal data structures. Yes the back end is locked and these users cannot navigate the database at will, but they do know what schema you have.
Also PowerBI is fine for dashboards but not very practical when providing long or even small lists
Mark Haring commented
For me it is crucial that the queries itself can not be seen or changed by other users. I am therefore really voting for YES. Protect the queries that I have worked *******.
The same applies for the data model.