How to disable 'peek' on Get and Transform

NickN

New Member
Joined
Jun 13, 2016
Messages
3
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
When I type Peek into the Excel Help (Excel 2011), I don't get any information.

What version of Excel are you using?
 
Upvote 0
Get and Transform is Excel 2016 (Windows, anyway)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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?
 
Upvote 0
LEFT][COLOR=

2hc9Etb


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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top