Set privacy levels (Power Query)

Privacy levels are critical to configure correctly so that sensitive data is only viewed by authorized users. Furthermore, data sources must also be isolated from other data sources so that combining data has no undesirable data transfer impact. Incorrectly setting privacy levels may lead to sensitive data being leaked outside of a trusted environment. Make sure you understand and set privacy to the appropriate level for your needs.

Security    If a data source contains highly sensitive or confidential data, set the privacy level to Private.

In May 2018, a European privacy law, the General Data Protection Regulation (GDPR), took effect. The GDPR imposed new rules on companies, government agencies, non-profits, and other organizations that offer goods and services to people in the European Union (EU), or that collect and analyze data tied to EU residents. The GDPR applies no matter where you are located.

Important    Although a restrictive isolation level blocks information from being exchanged between data sources, it may reduce functionality and impact performance.

  1. To display the Data Source Settings dialog box, do one of the following:

    Power Query Editor    Select File > Options and Settings > Data Source Settings.

    Excel    Select Data > Get Data > Data Source Settings.

  2. Select Edit Permissions. The Edit Permissions dialog box appears.

  3. Under Privacy Level, select an option from the drop-down list:

    None    There are no privacy settings. Be careful setting this option. Make sure that privacy regulations are otherwise maintained. You might use this privacy setting in a controlled development environment for testing and performance reasons.

    Private    Contains sensitive or confidential information, and the visibility of the data source may be restricted to authorized users. It is completely isolated from other data sources. Examples include Facebook data, a text file containing stock awards, or a workbook containing an employee review.

    Organizational    Limits the visibility of a data source to a trusted group of people. It is isolated from all Public data sources, but is visible to other Organizational data sources. A common example is a Microsoft Word document on an intranet SharePoint site with permissions enabled for a trusted group.

    Public    Gives everyone visibility to the data. Only files, internet data sources, or workbook data can be marked Public. Examples include data from a Wikipedia page, or a local file containing data copied from a public web page.

  4. Select OK

Privacy Levels may prevent you from inadvertently combining data from multiple data sources, which are set to different levels of privacy such as private and organizational. Depending on the query, you could accidentally send data from the private data source to another data source that might be outside of a trusted scope. Power Query analyzes each data source and classifies it by the defined level of privacy: Public, Organizational, and Private. This analysis ensures data is not combined if there is undesirable data transfer. This process of data protection can also occur when a query uses a technique called query folding. For more information about query folding, see Handling data source errors.

The Fast Combine options are workbook settings that determine whether Power Query uses your privacy level settings when combining data. These options are not enabled by default. 

Caution: Enabling Fast Combine by selecting Ignore the Privacy levels and potentially improve performance in the Workbook Settings dialog could expose sensitive or confidential data to an unauthorized person. Do not enable Fast Combine unless you are confident that the data source does not contain sensitive or confidential data.

  1. To display the Query Options dialog box:

    Power Query Editor    Select File > Options and settings > Query options.

    Excel    Select Data Get Data > Query Options​​​​​​.

  2. In the left pane, do one of the following:

    GLOBAL    Select Privacy, and then set one of the following in the right pane:

    • Always combine data according to your Privacy Level settings for each source    

    • Combine data according to each file's Privacy Level settings Privacy level settings are used to determine the level of isolation between data sources when combining data. This setting may decrease performance and functionality. Merging data across privacy isolation zones results in some data buffering.

    • Always Ignore the Privacy levels and potentially improve performance     Combine by ignoring your Privacy Levels setting. This setting can improve performance and functionality, but Power Query cannot ensure the privacy of data merged into the workbook.

    CURRENT WORKBOOK Select Privacy, and then set one of the following in the right pane:

    • Combine data according to your Privacy Level settings for each source Privacy level settings are used to determine the level of isolation between data sources when combining data. This setting may decrease performance and functionality. Merging data across privacy isolation zones results in some data buffering.

    • Ignore the Privacy levels and potentially improve performance     Combine by ignoring your Privacy Levels setting. This setting can improve performance and functionality, but Power Query cannot ensure the privacy of data merged into the workbook.

  3. Select OK.

See Also

Power Query for Excel Help

Privacy supplement for Excel

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×