Many new users to Power Query get confronted and confused about the Privacy Level dropdown.
This message appears when you download data from difference sources (OData, Excel, Sharepoint, etc.).
As a general rule, 99% of the time you will want to choose Organizational. This is because the nature of the data usually relates to your own company or organisation.
If you are after a more thorough understanding of the settings and how it’s used, keep reading.
This article explains what the Privacy Levels are for in Power Query, Power BI and Excel. It also explains how to configure them.
Table of Contents
- The risk of pulling data with Power Query
- Understanding the Privacy Firewall built into Power Query to secure your data
- How does the Privacy Firewall work?
- What Privacy Level should you set OdataLink data sources to?
- How to change the Privacy Levels of your data sources?
- How to disable the Privacy Firewall entirely?
The risk of pulling data with Power Query
You can pull and combine data from all kinds of sources using power query. This can range from data from OData Feeds, data within Excel workbooks, data stored in SQL Server or MySQL, etc.
Other than the source of the data, the nature of the data comes in three forms.
- Data you personally control and own.
- Data belonging to your organisation.
- Data from the web that is public.
Most steps you apply within Power Query act on the data without any risk. However, some steps can expose sensitive information.
The main steps that run this risk are:
- Filtering columns
- Invoking functions and passing parameters
- Merging tables of data
- Append tables of data
For these types of steps, you run the risk of sending and exposing sensitive information from one source to another.
As an example, you can build a power query to pull various dates, and then filter another endpoint with these dates. This process is called QUERY FOLDING. It occurs when Power Query sends information from one data source to another.
https://data.odatalink.com/Acc-Add-Sample-65472/xero/Dem-Com/TrialBalance(Date=2023-06-30,PaymentsOnly=false)
Dates are not dangerous, but imagine you are using employees Tax File Number or Social Security Numbers instead. You would not want to inadvertently pass this information to another data provider (say Google, Twitter or the ATO).
https://public.google.com/SomeEndpoint?$filter=TaxFileNumber eq '<hidden>'
Understanding the Privacy Firewall built into Power Query to secure your data
To secure your data, Power Query includes a Privacy Firewall. With this firewall in place (by default), you can specify the privacy nature of the data source. This is done via the Privacy Level dropdown.
Power Query provides four different options:
- Public
- Organizational
- Private
- None
Public
Public data is data that comes from a source outside your organisation and outside your control or ownership (such as Google, Twitter, the ATO, etc.).
Organizational
Organizational data is data that comes from your organisation and is not public to anyone else. This would include data stored in:
- Xero
- MYOB
- OdataLink
- Your SharePoint Servers
- Your organisation’s OneDrive account
- Your organisation’s servers
- Your organisation’s databases (SQL Server Databases, Azure SQL databases, MySQL) etc.
- Other websites or apps tied directly to your organisation to which only your organisation has access
Private
Private data is private to you as an individual.
None
This option should never be used and can prevent data from being folded.
How does the Privacy Firewall work?
Power Query will only fold data from a LESSER private data source to a MORE private data source.
- PUBLIC data can be sent to an ORGANIZATIONAL or PRIVATE data source.
- ORGANIZATIONAL data can be sent to a PRIVATE data source.
However, the inverse is not allowed. Power Query will not send data:
- From a PRIVATE data source to a PUBLIC or ORGANIZATIONAL one.
- From an ORGANIZATIONAL data source to a PUBLIC one.
If you do, one or two things will take place depending on your version of Power BI or Excel.
- You will receive a Power Query Firewall rule error message.
- The information will not be folded into your Power Query. Your Power Query will work but will not be optimised.
What Privacy Level should you set OdataLink data sources to?
You should set all data sources pulling from OdataLink to ORGANIZATIONAL.
For most other queries, refer to the definition above.
How to change the Privacy Levels of your data sources?
You can change the Privacy Levels of your data sources.
Changing the Privacy Levels in Power BI
To change the Privacy Levels of your data sources in Power BI, follow these steps.
- Open your Power BI workbook.
- From the ribbon, select File \ Options and Settings \ Data Source Settings.
- Select the data source to change and click Edit Permissions.
- Change the Privacy Level dropdown and click OK.
Changing the Privacy Levels in Excel
To change the Privacy Levels of your data sources in Excel, follow these steps.
- Open your Excel workbook.
- From the ribbon, select Data \ Get Data \ Data Source Settings.
- Select the data source to change and click Edit Permissions.
- Change the Privacy Level dropdown and click OK.
How to disable the Privacy Firewall entirely?
Alternatively, you can disable the Privacy Firewall entirely. We only advise doing so if:
- You fully understand how your Power Queries are folded.
- and there is no risk for sensitive data being exposed.
Disabling the Privacy Firewall in Power BI
To disable the Privacy Firewall in Power BI, follow these steps.
- Open your Power BI workbook.
- From the ribbon, select File \ Options and Settings \ Options.
- On the left-hand side, find Current File and select Privacy.
- Select Ignore Privacy Levels and potentially improve performance.
Disabling the Privacy Firewall in Excel
To disable the Privacy Firewall in Excel, follow these steps.
- Open your Excel workbook.
- From the ribbon, select Data \ Get Data \ Query Options.
- On the left-hand side, find Current Workbook and select Privacy.
- Select Ignore Privacy Levels and potentially improve performance.
