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.
Thanks
Joana
shared this idea
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.
thanks
Ashvini Sharma
Lead Program Manager
Excel
23 comments
-
jeff
commented
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.
-
Regan
commented
@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)
:-) -
Tam
commented
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.
-
Ed Hansberry
commented
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.
-
Ed Hansberry
commented
@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.
-
Regan
commented
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. -
Anonymous
commented
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.
-
Joana
commented
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 -
Ed Hansberry
commented
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.
-
Ryan
commented
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.
-
Randy
commented
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.
-
Vijay
commented
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.
-
Vijay
commented
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
Hi,
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.
-
Ken Puls
commented
Joana, I agree. Contrary to the anonymous comment, as a developer of BI information, there are times that I'll want a user to only be able to refresh and not modify the underlying query makeup. The ability to protect a Power Queries M code should exist.
-
Joana
commented
Hi Carl,
Of course, we use both Excel and Power BI. But with Excel environment only, is not possible to protect the Queries.
The point is, as well as you are able to protect the formulas, VBE, … and any other modifications in an Excel workbook, why this is not possible with Power Query.
Of course in some chats they explain how to do it, for ex. (http://stackoverflow.com/questions/37444536/how-to-password-protect-power-query-queries), but I think that the tool should let the users to do it without such alternative ways.
Thanks