# How to disable 'peek' on Get and Transform



## NickN (Jun 13, 2016)

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/ar...31-f7eb-4729-88dd-6a4921380ca9#__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


----------



## mikerickson (Jun 13, 2016)

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

What version of Excel are you using?


----------



## RoryA (Jun 13, 2016)

Get and Transform is Excel 2016 (Windows, anyway)


----------



## NickN (Jun 13, 2016)

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


----------



## ImkeF (Jun 13, 2016)

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.


----------



## NickN (Jun 16, 2016)

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


----------



## anvg (Jun 16, 2016)

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

```
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

```
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

```
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,


----------



## ImkeF (Jun 16, 2016)

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?


----------



## khiggs (Sep 7, 2019)

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. 
​


----------

