How do Privacy Levels work?

[justlogmein]

I am working with Power Query in Excel files and am constantly making changes to them and distributing to other users afterwards. The users frequently email me asking about the Privacy Level dialog box that pops up and I advise them to ignore them each time. But I want to understand what these levels actually do (and how to permanantly disable the pop-up for all users).

All of the articles on the web regarding these levels have been useless to be honest. What I don't understand is how exactly the system determines what is Organizational data and exactly what users have access to what, because I have not told it any of this information. Normally if I want to restrict users, I would use their computer user name and determine via some VBA if they were on a certain list. If I wanted to restrict it by project or groups of people, I would place the file in a network location only certain users had access to via the AD.

But the Power Query's defintion of Organizational and Privacy Levels is so vague I don't understand what it even does. And if I make a file Public, does that mean it gets published to the web or someone from the public can just access our stuff? If not, why label it public!? Would appreciate if someone could define in details how these things actually work, and how I can just disable the warnings permanetly.

[edhans]

I'll put it as simple as I can, because it isn't easy and the articles don't always help.

There are four privacy levels:

1. Private
2. Organizational
3. Public
4. None

Privacy levels prevent queries from passing data to each other when it would be a problem. For example, if you had a payroll report that had a social securty number in it and you were querying employee pay data vs an online report of competitive payroll rates to do a comparison, it is possible that Power Query would, rather than bring both data source in locally before merging, pass some personal info  (employee ID, ss#, etc) to the online source. Privacy levels prevent this.

None means there is no level set. Generally, in Excel, I always set it globally to none. Do that at your own risk, but all of my PQ stuff is company related and I don't want to fool with privacy levels between an on-prem SQL server and SharePoint list. I just want it to work.

Power BI handles privacy levels much better than Excel, so there I do it more correctly. I think it is because Power BI uses one user (report author or gateway) as the one with the permissions, and Excel does it for every single user that opens the file, so it is a mess.

Private data cannot be shared with any other queries. 2 private queries cannot talk to each other. They are private. You can only join/relate/use them together in the Power BI Data Model, where no communication goes back to the source anyway.

Organiziational Data can all talk to each other. CSV files on a network drive, SQL data, SharePoint LIsts, and even Salesforce.com and other online services, if marked "Organizational" can all talk to each other, so be careful. They cannot communicate with private or public sources. 

Public means anything goes. So I typically mark web pages I query as public. Public can talk to other public, but are blocked from talking to org/private.

Make a bit more sense?

Short answer - in Excel, turn it off. In Power BI, use it wisely as intended.

[justlogmein]

Thank you Edhans for your answer. I'm not entirely sure still how the privacy levels are determined though. I fully understand their intent, just not how they are defined. If there were some data sources on a network drive or wherever, how does Power Query know whether they are marked as Public, Private etc.? And is there a way for me to disable the pop-up so my users don't see it everytime I edit the file?

[edhans · Accepted solution]

You define them. Go to Power Query, click on the Data Sources icon, then click in a Data Source, then Edit Permissions. The privacy levels are there. I am not sure how it determines what gets assigned to what initially. I'm sure things like local network drives and on prem databases are organizational, and sites like IMDB.com are Public, but you have ultimate control.