Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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

225 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Joana shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    Accepting Votes  ·  AdminExcel Team [MSFT] (Admin, Office.com) responded  · 

    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

    22 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Regan commented  ·   ·  Flag as inappropriate

        @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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        @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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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.

      • Yann Courtel commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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

      • Carl Walsh commented  ·   ·  Flag as inappropriate

        I'm also not seeing what the motivation here is, maybe you can explain more Joana?

        If you share a link to official reports, are other users allowed to edit the workbook? If they can't save changes to the workbook then any query modification will always be done to their unofficial copy...

        BTW, if you wanted to share reports that users can consume but not edit, have you looked into PowerBI?

      ← Previous 1

      Feedback and Knowledge Base