- Get link
- X
- Other Apps
The purpose of this article is to explain in depth how Power Query privacy levels work, how they impact performance, query folding, and data refresh in Excel and Power BI, and how to configure them safely without breaking secure data isolation.
1. What Power Query privacy levels actually control
Power Query privacy levels are a data protection feature that controls how information from one data source can be combined with information from another data source in a single query.
When you import or connect to data, Power Query categorizes each source into one of several privacy levels and uses these categories to decide whether it is allowed to send data from one source to another (for example, when performing joins, merges, or parameterized web requests).
1.1 The core privacy levels
In current versions of Power Query (Excel and Power BI Desktop) you typically see three main privacy levels when configuring data sources.
| Privacy level | Intended scope | Typical examples | Fusion behavior |
|---|---|---|---|
| Private | Highly confidential, should never be sent to other sources. | HR salary tables, customer PII, internal finance data. | Power Query isolates the data and prevents it from leaking into other sources. |
| Organizational | Internal to your organization, safer to combine with other organizational data. | Corporate data warehouse, internal APIs, SharePoint lists. | Can be combined with other Organizational sources but not with lower-trust public sources. |
| Public | Data that can be shared freely without restriction. | Open data APIs, public CSV files on the web, open government data. | Can be combined broadly since there is no confidentiality expectation. |
1.2 The key concept: data source isolation
Power Query does not simply read each data source independently; it often needs to combine them during query evaluation.
For example, if you use values from an internal SQL table to build URLs for a web API, Power Query might need to send those values externally.
Privacy levels exist to prevent confidential information from being sent to less-trusted locations.
This protection is called data source isolation and is implemented in the Power Query engine by inserting internal “buffering” steps, duplicating queries, or splitting the query plan so that sensitive data is never sent directly to lower-trust sources.
2. How privacy levels impact performance in Power Query
Because privacy levels affect how queries are evaluated, they also have a direct impact on performance, refresh time, and resource usage.
2.1 Why performance can drop when privacy is enabled
When Power Query needs to respect privacy levels while combining sources, it may be unable to push transformations back to the data source (query folding) as efficiently as it could in an unrestricted environment.
Instead, the engine may have to:
- Download data from each source separately.
- Buffer results in memory or temporary storage.
- Perform joins, merges, and filters locally on the client rather than on the server.
This leads to higher CPU usage in the Power Query engine, more network I/O, and longer refresh times, especially when dealing with large tables.
2.2 Typical symptoms of privacy-related slowdowns
- A query that previously folded almost entirely to the data source now shows fewer or no folding indicators in Power Query diagnostics.
- Preview refresh and full refresh times increase significantly when you add a second data source to the same query.
- CPU usage on the client (Excel, Power BI Desktop, or the Power BI service) becomes a bottleneck while the underlying databases or APIs show low utilization.
Note : If a query pulls data from multiple sources with different privacy levels, assume that privacy constraints might force the engine to partially or fully evaluate the query locally instead of pushing logic to the back-end.
2.3 Examples: same query with and without strict privacy
Consider a model where you merge a large SQL Server table with a small CSV file that contains mapping codes.
- When both sources are set to Organizational and privacy is enabled, the engine may still be able to fold much of the merge back to SQL if the mapping table is also stored in a location considered safe for the merge.
- If the CSV is marked as Private and SQL as Organizational, Power Query often needs to pull both into the engine, buffer them separately, and perform the merge locally, eliminating most of the benefits of query folding.
3. Query folding and privacy levels
Query folding is the process of translating Power Query steps into native operations that run on the data source, such as SQL commands for relational databases.
Privacy levels influence how much of a query can be folded because they alter the logical shape of the query plan.
3.1 How privacy can break query folding
When sources with different privacy classifications are combined, the engine has to insert isolation steps.
These steps typically involve buffering or splitting the query into multiple subqueries that are later recombined in the local engine.
Once buffering occurs, operations after the buffer usually cannot be folded back to the data source, because the data is already materialized outside of the original source.
3.2 Diagnosing folding issues related to privacy
To analyze folding behavior in practice, you can:
- Right-click on steps in the Power Query Editor and use options such as “View Native Query” (where available).
- Use built-in query diagnostics to inspect which steps are executed on the source versus locally.
- Temporarily adjust privacy settings in a safe test environment to compare performance and folding behavior.
Note : Never disable or relax privacy settings in production just to gain performance unless you fully understand the data classification and have organizational approval to change the security posture.
4. Privacy levels and data fusion scenarios
Data fusion is the process of combining, merging, or referencing multiple sources in one Power Query solution.
Privacy levels are evaluated at the data source level, which means that even indirectly related queries can be affected when data is reused across queries.
4.1 Common fusion patterns that trigger privacy enforcement
- Using values from one source to build a parameter or filter for another source (for example, using customer IDs from a Private table to parameterize an API call).
- Merging or appending tables from different origins, like combining an internal database with a public CSV file.
- Creating custom functions that receive values from one source and call another source internally.
4.2 Safe fusion design techniques
To minimize performance impact while preserving security, design your data fusion with privacy-aware patterns:
- Keep highly sensitive data (Private) in separate queries that are loaded to the data model but not directly merged with less sensitive sources.
- Where possible, pre-join data inside the same source system (for example, within the same SQL database) instead of having Power Query join across systems.
- Use Organizational level consistently for trustworthy internal systems that can be safely combined.
- Restrict Public data to non-sensitive contexts where it does not need to be combined with private information.
5. Configuring privacy levels correctly
Configuring privacy levels correctly is a balance between security, compliance, and performance.
5.1 Where to configure privacy levels
In Excel or Power BI Desktop, you typically configure privacy levels via the data source settings dialog.
- Go to Data Source Settings and select the specific data source.
- Choose Edit Permissions and then set the Privacy Level for that source.
- Repeat for each source used in your Power Query solution.
5.2 Choosing the right level for each source
Use the following practical guidelines when assigning levels:
| Source type | Recommended default | Reasoning |
|---|---|---|
| Enterprise data warehouse / internal SQL Server | Organizational | Contains internal data that may be combined with other corporate sources while remaining inside the organization. |
| HR, payroll, or sensitive finance systems | Private | Contains confidential data; must not be sent to other sources without explicit control. |
| Public open data APIs and CSV files | Public | Information is already public; there is no confidentiality expectation. |
| Departmental SharePoint lists with internal content | Organizational | Internal but not strictly confidential; safe to combine with other organizational sources within policy. |
5.3 Global privacy configuration options
In some environments, there are global options that allow you to turn privacy enforcement off, set it to always combine data according to privacy levels, or ignore privacy checks entirely during certain operations.
These options can change behavior for all queries in the workbook or report, so they should be handled carefully and typically governed by organizational policy rather than individual choice.
Note : If your organization has regulatory or contractual obligations (for example, related to personal data protection), disabling or loosening privacy enforcement can introduce compliance risk and should be avoided without formal approval.
6. Privacy levels in Excel vs Power BI
While both Excel and Power BI use the Power Query engine, they can differ in how privacy settings are applied during refresh, especially in the Power BI service.
6.1 Desktop refresh vs service refresh
When you refresh in Power BI Desktop, privacy is evaluated locally based on the data source settings configured in the Desktop file.
When you publish to the Power BI service, the service also needs privacy information for gateways and cloud data sources.
Misalignment between desktop settings and service-side settings can lead to different performance characteristics or even refresh failures.
6.2 Data gateways and on-premises sources
If you use an on-premises data gateway, you need to consider how privacy levels are defined both in the report and within the gateway configuration.
Consistent classification helps ensure predictable performance and secure data movement during scheduled refresh.
7. Best practices to balance privacy and performance
To use Power Query privacy levels effectively, follow a structured set of best practices.
7.1 Classify your data sources centrally
- Create a list of common data sources (databases, APIs, files, SharePoint sites) and assign each a default privacy level.
- Document the rationale and ensure project teams use the same classification consistently.
7.2 Optimize queries for folding first
- Design queries so that as much work as possible happens in a single source with a uniform privacy level.
- Avoid unnecessary cross-source operations in Power Query when they can be performed earlier in the data pipeline.
- Use views or stored procedures in databases to pre-join or pre-aggregate data before it reaches Power Query.
7.3 Separate highly sensitive data paths
- Keep Private data in dedicated queries and data models that do not depend on or feed into less-trusted sources.
- When analytics requires combining sensitive and non-sensitive data, consider doing so in a secure, governed environment rather than directly within Power Query.
7.4 Monitor performance and adjust designs
- Regularly review refresh times and query diagnostics to identify privacy-induced performance bottlenecks.
- Iteratively refactor queries to reduce the need for cross-source fusion where possible.
8. Practical checklist for Power Query privacy levels
The following checklist can be used when building or reviewing a Power Query solution.
| Checklist item | Question | Action if not satisfied |
|---|---|---|
| Data classification assigned | Have all data sources been assigned an appropriate privacy level? | Review each source in Data Source Settings and set the correct level. |
| Cross-source merges reviewed | Are you merging or appending tables from sources with different privacy levels? | Consider refactoring so that the merge happens in a single source or in a secure upstream system. |
| Query folding validated | Do key queries still fold as expected after privacy is configured? | Use query diagnostics and native query views to verify folding; adjust design if folding is lost unnecessarily. |
| Service vs desktop alignment | Are privacy levels consistent between Desktop and the Power BI service or gateway? | Align configurations, especially when scheduled refresh behaves differently from local refresh. |
| Compliance checked | Do chosen privacy settings satisfy organizational and regulatory requirements? | Consult with data protection or compliance teams before changing global privacy options. |
FAQ
Does disabling Power Query privacy levels always improve performance?
No. While disabling privacy enforcement can remove some buffering and allow more query folding, it does not automatically guarantee better performance in every scenario.
More importantly, turning off privacy protections may allow sensitive data to be sent to less-trusted sources, which can create security and compliance risks.
Should all internal data sources be marked as Organizational instead of Private?
Not necessarily.
Highly sensitive systems, such as HR or payroll databases, may still require the Private level even if they are internal.
Use Organizational for sources that are internal and broadly safe to combine with other corporate systems, and reserve Private for data that must not be shared or fused without strict control.
How can I see if privacy is affecting query folding?
You can compare the query behavior with different privacy configurations in a safe test environment.
Use diagnostics tools and native query views to see how much logic is pushed to the data source; if folding drops significantly when strict privacy is applied, privacy levels are likely affecting performance.
Is it safe to set public web data to Public privacy level?
Yes, in most cases.
Data that is already public and does not contain confidential information is a good candidate for the Public level.
However, always follow your organization’s data classification policy, especially if you combine public data with internal identifiers or metrics.
What is the best way to design queries with multiple sources?
Start by classifying each source, then design the solution so that most heavy operations occur within a single, well-trusted source whenever possible.
Use Power Query primarily as an orchestration and light transformation layer rather than relying on it to perform large cross-source joins that depend heavily on privacy fusion logic.