Privacy Levels in Power BI: Securing Your Data Transformations

When you first start working with Power BI, you might encounter a frustrating pop-up window asking you to define "Privacy Levels" for your data sources. Many users, in a hurry to build their first report, simply select "Public" for everything or check the box to "Ignore Privacy Levels" just to make the message go away. However, Privacy Levels are not just a bureaucratic hurdle or an annoying prompt; they are a fundamental security feature of the Power Query engine designed to prevent accidental data leakage.

Understanding how Privacy Levels work is essential for any data professional. These settings determine how the Power Query engine (the "Firewall") handles data when you try to combine information from multiple sources. If you don't configure these correctly, you might run into the dreaded "Formula.Firewall" error, or worse, you might inadvertently send sensitive internal data to an external, untrusted web service. In this lesson, we will dive deep into what Privacy Levels are, how the Data Privacy Firewall operates, and how to manage these settings effectively in both Power BI Desktop and the Power BI Service.

What are Privacy Levels?

At its core, a Privacy Level is a classification you assign to a data source to tell Power BI how much you trust that source and how freely its data can be shared with other sources during a data transformation process. Power BI uses these levels to build a "firewall" that isolates data. This isolation ensures that sensitive data from one source isn't sent to another source where it shouldn't be.

Imagine you are working with two data sources: an internal SQL Server database containing private customer salaries and a public Web API that provides currency exchange rates. You want to convert those salaries into different currencies. To do this, Power Query might need to send a "request" to the Web API. If the Privacy Levels aren't set correctly, Power BI might worry that by sending a request to the API, it is accidentally "leaking" information about your internal database (like which specific employees or salary ranges you are querying) to the public internet.

There are four primary Privacy Levels you can assign to a data source:

1. Private: This is the most restrictive level. Data from a Private source is completely isolated. It cannot be shared with any other data source during the transformation process. This is intended for your most sensitive information, such as HR records, financial secrets, or personal identifiable information (PII).
2. Organizational: This level is intended for data that is trusted within your company or group but is not public. Data from an Organizational source can be shared with other Organizational sources or with Private sources (though the Private source remains isolated). It cannot be shared with Public sources.
3. Public: This is the least restrictive level. Data marked as Public can be seen by anyone. Power BI assumes there is no risk in sharing this data or sending it as part of a request to any other data source. Examples include public census data, Wikipedia tables, or open weather APIs.
4. None: This is the default state for a new data source before you have configured it. While it technically exists, Power BI will eventually force you to choose one of the other three levels before it allows you to refresh the data or combine it with other sources.

Callout: Privacy Level Hierarchy

Think of Privacy Levels as a set of concentric circles of trust.

• Private is the inner sanctum. It can see out, but nothing can see in, and it doesn't talk to anyone else.
• Organizational is the corporate office. People inside can talk to each other, but they don't share secrets with people on the street.
• Public is the town square. Everyone can see what is happening here, and there are no secrets to protect.

The Data Privacy Firewall

The Data Privacy Firewall is the internal mechanism within Power Query that enforces these Privacy Levels. When you hit "Refresh" or "Apply Changes," the Firewall analyzes your M code (the language behind Power Query) to see how data moves between sources. It looks for "Data Combinations."

A data combination occurs whenever the output of one query or data source is used as an input for another. For example, if you have a list of product IDs in an Excel file and you use those IDs to filter a SQL Server table, you are combining data. The Firewall checks the Privacy Levels of both the Excel file and the SQL Server. If the Excel file is "Private" and the SQL Server is "Public," the Firewall will block the operation to prevent the product IDs (the private data) from being sent to the public server.

The "Formula.Firewall" Error

The most common way users encounter the Firewall is through an error message that looks something like this: Formula.Firewall: Query 'Query1' (step 'StepName') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

This error usually happens because you are trying to perform a "cross-source" join or merge where the privacy settings are incompatible. It can also happen even if the settings seem compatible because the Power Query engine is trying to optimize the query by "folding" it (sending the work to the data source), and it realizes that doing so would violate a privacy boundary.

Practical Examples of Privacy Level Conflicts

To understand how to manage these levels, let's look at a few real-world scenarios where the Firewall might step in.

Scenario 1: The Web API Leak

Suppose you have an Excel spreadsheet on your local computer marked as Private. This spreadsheet contains a list of high-value client names. You want to use a public Google Maps API (marked as Public) to get the GPS coordinates for these clients' office addresses.

When you try to merge these in Power Query, the engine realizes that to get the coordinates, it has to send the addresses from your Private Excel file to the Public Google API. The Firewall blocks this. Even if the data is just an address, Power BI doesn't know what is sensitive and what isn't; it only knows that "Private" data is attempting to leave its boundary and enter a "Public" one.

Scenario 2: The Organizational Database

Imagine your company has a SQL Server database containing sales data, which you have marked as Organizational. You also have a SharePoint list containing a list of "Sales Targets," also marked as Organizational.

In this case, Power BI will allow you to merge or join these two sources. Because they are both within the same "Organizational" boundary, the engine assumes they are part of the same trust network. No data is "leaking" outside the company, so the Firewall permits the combination.

Scenario 3: The "None" Level Confusion

If you are developing a report and you connect to a new SQL database but haven't set a privacy level yet, it defaults to None. If you then try to merge this with an existing Organizational source, Power BI will pause and prompt you to define the level for the new source. It will not allow the refresh to proceed until the ambiguity is resolved.

Note: Privacy levels are set per data source, not per query. If you have five different queries all hitting the same SQL Server, they all share the same Privacy Level setting for that server.

How to Configure Privacy Levels

Managing these settings is a two-step process: you manage them in Power BI Desktop during development, and you manage them in the Power BI Service (or via a Data Gateway) once the report is published.

Setting Privacy Levels in Power BI Desktop

To set or change the privacy levels for your data sources in the Desktop application, follow these steps:

1. Open Power BI Desktop.
2. Go to the File menu in the top left corner.
3. Select Options and settings, then click on Data source settings.
4. In the dialog box that appears, you will see a list of all data sources used in the current file.
5. Select a data source and click Edit Permissions at the bottom.
6. Under the Privacy Level dropdown, select the appropriate level (Public, Organizational, or Private).
7. Click OK and then Close.

Alternatively, you can manage global privacy settings that apply to all your Power BI files:

1. Go to File > Options and settings > Options.
2. Under the Global section on the left, select Privacy.
3. Here you can choose how the firewall behaves by default. However, it is usually better to manage these settings at the Current File level to ensure specific report security.

Setting Privacy Levels in the Power BI Service

Once you publish a report, the settings you chose in Desktop are often uploaded with it, but the Power BI Service (the cloud environment) has its own way of handling these, especially if you are using an On-premises Data Gateway.

1. In the Power BI Service, go to the Workspace where your report is located.
2. Find the Dataset (now often called Semantic Model) associated with your report.
3. Click the three dots (...) and select Settings.
4. Expand the Gateway connection or Data source credentials section.
5. For each data source, you will see an option to "Edit credentials." Within that popup, you must define the Privacy Level for the cloud environment.

Warning: If the Privacy Levels in the Power BI Service do not match the logic used in your Power Query transformations, your scheduled refreshes will fail with a "Formula.Firewall" error, even if the report worked perfectly on your local computer.

The "Ignore Privacy Levels" Setting

There is a setting in Power BI Desktop under Options > Current File > Privacy labeled "Ignore the Privacy Levels and potentially improve performance."

This is often referred to as "Fast Combine." When this is enabled, the Data Privacy Firewall is essentially turned off for that specific file. Power BI will no longer check if data is moving between Private and Public sources.

Why would you use this?

• Development Speed: It stops the annoying pop-ups and firewall errors during the initial build phase.
• Performance: The Firewall adds overhead. By ignoring privacy levels, Power Query can sometimes optimize queries more aggressively, leading to faster refresh times.

Why should you avoid this?

• Security Risk: This is the primary reason. If you are working with truly sensitive data, ignoring privacy levels means you are removing the safety net that prevents that data from being sent to external APIs or untrusted sources.
• Deployment Issues: If you develop a report with "Ignore Privacy Levels" turned on, it might work locally, but it will fail when published to the Power BI Service unless you also configure the Service to ignore privacy levels (which is not always possible or recommended in a corporate environment).

Callout: When to use "Ignore Privacy Levels"

You should only use the "Ignore Privacy Levels" setting if you are 100% certain that your data sources do not contain sensitive information that could be leaked, or if you are working in a completely isolated environment where all data sources are already highly secured and trusted. In a professional enterprise environment, it is almost always better to configure the levels correctly rather than ignoring them.

Deep Dive: M Code and the Firewall

To understand why the firewall triggers, we need to look at how Power Query evaluates code. Power Query uses "lazy evaluation," meaning it doesn't calculate anything until it absolutely has to. It also tries to "fold" operations. Query folding is when Power Query converts your transformation steps (like filtering or joining) into a single SQL statement that is executed by the source database.

The Firewall becomes a problem when folding meets privacy boundaries.

Example of a Firewall-Triggering Query

Consider this M code snippet where we try to filter a SQL table based on a value retrieved from a Web API:

let
    // Get the current discount rate from a Public Web API
    SourceAPI = Json.Document(Web.Contents("https://api.example.com/discount")),
    CurrentDiscount = SourceAPI[rate],

    // Connect to the internal Sales database (Organizational)
    SqlData = Sql.Database("ServerName", "DbName"),
    SalesTable = SqlData{[Schema="dbo", Item="Sales"]}[Data],

    // Filter the sales where the amount is greater than the discount rate
    FilteredSales = Table.SelectRows(SalesTable, each [Amount] > CurrentDiscount)
in
    FilteredSales

In this example:

1. SourceAPI is Public.
2. SqlData is Organizational.
3. The FilteredSales step tries to use a value from a Public source to filter an Organizational source.

The Power Query engine wants to "fold" this. It wants to send a command to the SQL Server that says SELECT * FROM Sales WHERE Amount > [CurrentDiscount]. However, to do that, it has to send the value of CurrentDiscount into the SQL Server environment. While going from Public to Organizational is usually okay, the Firewall is extremely cautious about any cross-boundary movement that isn't explicitly defined.

How to Fix the "Formula.Firewall" Error Without Disabling Security

Instead of turning off the firewall, you can often fix the error by restructuring your queries. One common technique is to ensure that "parameter-like" values are separated from the main data retrieval.

Another common fix involves using the RelativePath and Query options in the Web.Contents function. This often helps the firewall understand that the dynamic parts of a URL are not "leaking" data but are part of a structured request.

// Instead of this:
// Source = Json.Document(Web.Contents("https://api.com/data?id=" & InternalID))

// Use this:
Source = Json.Document(Web.Contents("https://api.com/data", [Query=[id=InternalID]]))

Using the Query record helps the engine separate the base URL (the source) from the parameters (the data), which can sometimes satisfy the firewall's requirements for data isolation.

Comparison Table: Privacy Level Characteristics

Best Practices for Privacy Levels

To maintain a secure and functional Power BI environment, follow these industry-standard best practices:

1. Be Consistent

Ensure that the Privacy Levels you set in Power BI Desktop match the levels you set in the Power BI Service/Gateway. If you set a SQL database to "Organizational" in Desktop but leave it as "Public" in the Gateway, your refresh will likely fail or behave unpredictably.

2. Use "Organizational" as the Default

For most corporate data sources (SQL servers, SharePoint lists, Excel files on company drives), Organizational is the most appropriate setting. It provides a good balance between security and the ability to combine data from different internal systems.

3. Reserve "Private" for the Essentials

Only use the Private level for data that truly needs to be isolated. If you mark everything as Private, you will find it nearly impossible to create useful reports because you won't be able to join or merge any of your tables.

4. Avoid "Ignore Privacy Levels" in Production

While it is tempting to check this box to make errors go away, it is a poor practice for production reports. It bypasses security controls and can lead to data being sent to places it doesn't belong. If you encounter a firewall error, take the time to understand the data flow and fix the query or the settings properly.

5. Document Your Sources

Keep a record of why certain sources are marked as Private. This is helpful for audits and for other developers who might take over your work. If a data source contains GDPR-regulated data, for instance, it should be clearly marked and set to the appropriate privacy level.

Common Pitfalls and How to Avoid Them

Pitfall 1: Mixing Cloud and On-premises Data

When you combine a cloud source (like an Azure SQL Database) with an on-premises source (like a local Excel file via a Gateway), the Firewall becomes very strict.

• The Fix: Ensure both are set to "Organizational." If the cloud source is set to "Public" and the local source is "Organizational," the combination will fail.

Pitfall 2: The "Blank" Privacy Level

Sometimes, after an update or a change in data source credentials, the privacy level might be reset to "None" or become blank. This often results in a generic "Information is required about data privacy" error.

• The Fix: Always check the Data source settings first when a refresh fails unexpectedly. It is the most common cause of refresh issues.

Pitfall 3: Over-reliance on the "Public" Setting

Some developers set everything to "Public" just to avoid the firewall. This is dangerous because it tells Power BI that it is okay to send any of that data to any web service you might connect to in the future.

• The Fix: Take five minutes to classify your data correctly. It is a one-time setup that ensures long-term security.

Common Questions (FAQ)

Q: Does changing the Privacy Level change the actual permissions on the database? A: No. Privacy Levels in Power BI only affect how the Power Query engine handles the data after it has been retrieved or during the retrieval process. It does not grant or revoke access to the underlying SQL Server or file system. You still need the correct usernames and passwords to access the data.

Q: Why does my report work in Desktop but fail in the Service? A: This is usually because the Privacy Levels in the Power BI Service (configured in the Gateway or Dataset settings) are different from the ones in your Desktop file. The Service does not always "inherit" these settings perfectly, so you must verify them in the cloud.

Q: Can I set Privacy Levels using M code? A: No, Privacy Levels are a metadata setting managed by the Power BI application and the Gateway. They are not part of the M script itself. This is a security feature—you wouldn't want a script to be able to lower its own security requirements!

Q: Does "Public" mean the whole world can see my report? A: No. "Public" just means that Power BI thinks the source data is public. Your report's visibility is still controlled by your Power BI Workspace permissions and how you share the report.

Summary and Key Takeaways

Privacy Levels are a vital component of the Power BI ecosystem. They act as the rules of engagement for the Data Privacy Firewall, ensuring that sensitive information stays where it belongs. While they can occasionally cause "Formula.Firewall" errors, these errors are usually a sign that you are attempting a data combination that could potentially risk data exposure.

By understanding the difference between Public, Organizational, and Private levels, you can build reports that are not only powerful and performant but also secure and compliant with your organization's data handling policies.

Key Takeaways:

• Privacy Levels prevent data leakage: They ensure that sensitive data from a Private or Organizational source isn't inadvertently sent to an external Public source.
• The Firewall is the enforcer: The Power Query engine uses these levels to build boundaries. If your M code tries to bridge these boundaries in an unsafe way, the Firewall will block the query.
• Organizational is the "Sweet Spot": Most business data should be set to Organizational to allow for internal data combinations while maintaining a barrier against the public internet.
• Consistency is critical: Ensure your settings in Power BI Desktop match your settings in the Power BI Service and the Data Gateway to avoid refresh failures.
• Avoid "Ignore Privacy Levels" in production: Only use this setting for non-sensitive data or during very early stages of development. It removes a critical layer of security.
• Fix the query, don't just lower the bar: If you hit a firewall error, try to use RelativePath in Web.Contents or restructure your merges rather than simply setting everything to "Public."
• Settings are per-source: Remember that a Privacy Level applies to the entire data source (e.g., the whole SQL server), not just a single table or query.