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
Thank you for taking your time to suggest and vote for being able to protect Power Query with a password. This is a good idea, but we don’t think we’ll be able to support this in the near future. We’ll continue tracking votes for it.
Guy Hunkin
Excel Team
26 comments
-
Mwamba
commented
I not only need to protect the query but also hide the source data as I cannot select specified colums when retrieving it.
-
Wonderin
commented
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.
-
TonyDC
commented
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.
-
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