Power Query Formula.Firewall

What is the Power Query Formula.Firewall?

Power Query Formula.Firewall is an error raised by Power Query’s Data Privacy Firewall. The purpose of raising such an error is to prohibit users from passing any unintentional information between the different data sources. The Power Query Formula.Firewall security feature helps you enforce the data privacy requirements and curb any unintended data leakage to external users or systems.

Let’s understand this with the help of an example. Let’s say we are joining SQL data containing customers’ bank account details (which is sensitive information) with the results of any OData feed, and suddenly, you notice that the bank account numbers are being provided as inputs to the OData service. It can potentially breach privacy levels, compromising the customer’s bank accounts. Power Query’s Data Firewall ensures that it prevents users from passing such unintended private information between data sources to ensure your information is safe.

Types of Formula.Firewall errors

There are two types of Formula.Firewall errors that can be raised in Power Query. These include:

#1 – Reference other queries or steps error message:

• This error type is raised in those scenarios where the formula firewall fails to function accurately due to the format of the query structure created.
• To fix such types of errors, flattening the queries can be used. It ensures that the information is directly passed to the source, enabling the gateway to operate normally.
• The error message is as follows.

#2 – Privacy levels error message:

• This type of error occurs in cases where Power Query fails to determine how to combine two data sources that have incompatible privacy levels.
• For example, One of the data sources has a public privacy level, whereas the other one has either an organizational or private privacy level.
• To fix such type of error,
• You can configure privacy level settings always to ignore, i.e., disable altogether or
• Apply the appropriate privacy level for each applicable data source explicitly.
• The error message looks like the one below.

How to fix the Formula.Firewall error in Power Query?

Depending on the type of Formula.Firewall error message, you can take appropriate steps to fix the Formula.Firewall error in Power Query.

If the Formula.Firewall error in Power Query is related to the reference to other queries or steps, you must consider flattening the queries option to fix it.

Flattening the queries

To understand how this fix works, let’s understand this through an example.

Let’s consider that we have a query to scan through the ISO country codes from a Countries file and amend these codes with quotes in a CSV file.

— Read ISO Country Code and Format in Quotes
let
Source = Csv.Document(
File.Contents(“C:\Users\dhyan\Downloads\Countries.csv”),
[Delimiter = “,”, Columns = 1, Encoding = 1252, QuoteStyle = QuoteStyle.None]
),
#”Promoted Headers” = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Added_Column = Table.AddColumn(#”Promoted Headers”, “Quoted”, each “‘” & [Country] & “‘”),
To_Text = Text.Combine(Added_Column[Quoted], “,”)
in
To_Text

There is another query to extract Profit information referencing the Read ISO Country Code and Format in Quotes query and accordingly pass the results to the SQL server.

— Profit Details
let
Source = Sql.Database(
“everydaybi-sql-server-dev.database.windows.net”,
“everydaybi-sql-dw-dev”,
[ Query = “SELECT
[Invoice Date]
,[ISO]
,[ID Business Area]
,[ID Product Line]
,[Net Profit]
FROM [everydaybi].[fact_profit_view]
WHERE [ISO] IN ( ” & Country & “)”,
CreateNavigationProperties = false
]
)
in
Source

When Power Query evaluates the Profit Details query, the Firewell raises a reference error as the query violates the rule by referencing a data source and another partition (Read ISO Country Code and Format in Quotes) in the same partition.

To fix this error, you can flatten and combine both the queries Read ISO Country Code and Format in Quotes and Profit Details

— Profit Flattened
let
Source = Csv.Document(
File.Contents(“C:\Users\dhyan\Downloads\Countries.csv”),
[Delimiter = “,”, Columns = 1, Encoding = 1252, QuoteStyle = QuoteStyle.None]
),
#”Promoted Headers” = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Added_Column = Table.AddColumn(#”Promoted Headers”, “Quoted”, each “‘” & [Country] & “‘”),
To_Text = Text.Combine(Added_Column[Quoted], “,”),
Get_Table = Sql.Database(
“everydaybi-sql-server-dev.database.windows.net”,
“everydaybi-sql-dw-dev”,
[ Query = “SELECT
[Invoice Date]
,[ISO]
,[ID Business Area]
,[ID Product Line]
,[Net Profit]
FROM [everydaybi].[fact_profit_view]
WHERE [ISO] IN ( ” & To_Text & “)”,
CreateNavigationProperties = false
]
)
in
Get_Table

This will resolve the reference errors and execute the query.

If you have encountered the privacy levels error message in Power Query, you can fix such errors either by

• Ignoring privacy entirely or
• Using the correct setting for each data source

Let’s look at each of these solutions to understand it better.

Ignoring Privacy Entirely

This fix involves ignoring the data privacy configurations entirely. You may consider this option only when you are confident that no sensitive information will be passed to the query.

To set up this configuration, follow the steps outlined below:

Step 1: Open Power Query in your machine.

Step 2: Navigate to File – Options and settings – Options

It will open an Options dialog box.

Step 3: In the Options window, click on Privacy on the navigation menu and then choose the Always ignore Privacy Level settings option. Click on OK.

Note: This setting may pass sensitive or private information to unauthorized users. Hence, use this option only when you are not using any confidential information for your query.

Step 4: Navigate to Refresh Preview and select Refresh Preview from the dropdown menu.

It will remove the error message you have encountered in Power Query, and your query will start functioning smoothly.

Using the correct setting for each data source

This fix involves setting up the privacy level at each data source level as opposed to ignoring the data privacy configurations entirely.

To configure the data privacy settings for each data source, follow the instructions highlighted below:

Step 1: Open the Power Query Editor in your system.

Step 2: Navigate to File – Options and settings – Data source settings.

It will open a Data source settings window.

Step 3: In the Data source settings window, select the data source and then click Edit Permissions.

Step 4: In the Edit Permissions window, choose a Privacy Level from the dropdown menu for your dataset.

These Privacy Levels settings include:

• None: No privacy level is applicable to your data sources. It is different from the ignoring privacy level option.
• Public: This setting enables you to share the data publicly with all the data sources.
• Organizational: You can share the data with data sources within your organization.
• Private: This setting is strictly confidential and should not be shared with other sources.

Step 5: Click on OK to save the Privacy Level changes.

Note: You can also configure the Global permissions on the dataset by using the similar steps highlighted above.

Important Things to Note

• It is highly recommended that you perform a deep dive into your dataset to understand any sensitive or confidential information and data privacy implications.
• Currently, Power Platform dataflows don’t have privacy levels functionality available.
• Ensure that you have correctly set up the data source permissions and privacy settings for your datasets to avoid any unintentional data leakage.
• Keep checking the Microsoft updates on new features and documentation to avail the latest features.
• Privacy levels of your dataset can bring down the performance of Power Query processes.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Power Query Formula.Firewall. Here we learn the types, how to fix them in power query, correct settings and points to remember. You can learn more from the following articles –

• Query Folding in Power Query
• Power Query in Excel
• Power BI Query Dependencies