Results 1 to 9 of 9

Thread: How to disable 'peek' on Get and Transform

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to disable 'peek' on Get and Transform

    Hi

    I'm trying to create a budget helper for charity staff to make sure costs are calculated correctly when they put together budgets for funders. Some of the source data is confidential - ie Staff Salaries and needs updating regularly by different teams, so I've created source data in different excel worksheets and I've used Get and Transform to bring the data into the sheet that staff will use, I have then set the resulting sheets as 'very hidden'. I have also locked cells to stop people inputting into the wrong place, and protected the workbook. However I've found that staff will still be able to see all the confidential data simply by clicking on the Data Tab/Get and Transform/Show Queries. This brings up a side window which then show the data contained within the query through the 'peek' facility. This effectively means that everyone will be able see the complete salary list, which is clearly not desirable.

    I've tried looking at the privacy settings for Get and Transform, but whenever I go to Data Source Settings to set privacy (https://support.office.com/en-gb/art...__toc354511927) in the hope that this will resolve the issue, it says 'you don't have any data source entries saved on this computer'.

    I'm very new to this and any help that could be provided would be very gratefully received.

    Many thanks

    Nick

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,597
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to disable 'peek' on Get and Transform

    When I type Peek into the Excel Help (Excel 2011), I don't get any information.

    What version of Excel are you using?

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to disable 'peek' on Get and Transform

    Get and Transform is Excel 2016 (Windows, anyway)

  4. #4
    New Member
    Join Date
    Jun 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to disable 'peek' on Get and Transform

    Hi

    Sorry yes it's Excel 2016, used to be called Power Queries I believe. It took me a while and lots of googling to work out what the 'peek', basically it's a pop up window that appears with all the data in it if you hover over the query.

    Thanks

    Nick

  5. #5
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to disable 'peek' on Get and Transform

    I would keep confidential data in a separate file and create an aggregated query/table there that will be referenced by the budget-entry-tables. Only non-confidential data should be imported into the files that are used for budget entry.
    __________________________
    Imke

    Blog: www.TheBIccountant.com

  6. #6
    New Member
    Join Date
    Jun 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to disable 'peek' on Get and Transform

    Thanks for the suggestion, I'm not entirely sure I follow. Ultimately the charity provides support and therefore the bulk of the costs are staff time. This means that we need to reference the data on Staff Salaries (with on-costs etc), and obviously it is possible for people doing the maths to work out different role's salary bandings - we're ok with that as it's an inevitability of funding requests to funders. This method means its not very straightforward to get that data from the spreadsheet, however the peak in the PowerQuery (Get and Tranform) shows effectively raw data as a Vlookup - so 'role' and average salary. This is simply available for anyone to see without them having to do any of the maths to get to individual role salaries and effectively hands them on a plate all the salaries, this is why I wanted to disable the peak if possible.

    If I have misunderstood what you were suggesting then please do let me know, as I really do want to solve this problem and create something which means we have accurate budgets produced for funders, but which doesn't disclose every role's salary.

    Thanks

  7. #7
    Board Regular anvg's Avatar
    Join Date
    Feb 2012
    Location
    Sankt-Peterburg
    Posts
    461
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to disable 'peek' on Get and Transform

    Hi
    If I understand you rightly you want to hide all steps of PQ query. If your users are non-advanced (they do not know about advanced editor of PQ query) then you can hide those steps with a simple method by moving a last step of the query after IN statement.
    Let we have such code
    Code:
    let
        //sales table on a hidden sheet
        sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
        //query table on a hidden sheet
        query = Excel.CurrentWorkbook(){[Name="Query"]}[Content],
        return = Table.Join(sales,{"CheckId","ProductId"},query,{"CheckId","ProductId"})
    in
        return
    Transform it into
    Code:
    let
        sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
        query = Excel.CurrentWorkbook(){[Name="Query"]}[Content],
    in
        Table.Join(sales,{"CheckId","ProductId"},query,{"CheckId","ProductId"})
    The users can see only a result of query.
    It is other variant. PQ is a functional language we can transform query to one formula
    Code:
    let
    in
        Table.Join(Excel.CurrentWorkbook(){[Name="Sales"]}[Content],{"CheckId","ProductId"},Excel.CurrentWorkbook(){[Name="Query"]}[Content],{"CheckId","ProductId"})
    If the users do not known M-language then they cannot rewrite the query to a form which allows them to see "Sales" table data.
    Regards,

  8. #8
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to disable 'peek' on Get and Transform

    Might be that we need to deal with a completely different problem here:
    If your users shall be able to refresh the queries, they need access to the files that are queried. Won't this enable them to view all details no matter how you're going to hide the steps in PQ?
    __________________________
    Imke

    Blog: www.TheBIccountant.com

  9. #9
    New Member
    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to disable 'peek' on Get and Transform

    [img]
    https://flic.kr/p/2hc9Etb
    [/img]


    NickN wants to disable the preview that shows when you mouse over a Query under Queries & Connections. I'm running into the same issue - I have underlying queries that are not visible in the workbook itself, and I don't want users to be able to mouse over a query and see all the results for a Connection Only query. I can't figure out how to turn off the "peek" function. In addition to popping up when mousing over a Query, if you right click on the Query (or Connection) there's a menu option "Show the Peek." This is what I'd like to turn off.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •