Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
gampy
Advocate I
Advocate I

Power Query’s Data Privacy Firewall

Hello Community!

I get a well-known firewall error when I try to embed data received from another source (DataFlow) into an SQL subquery. The option "Always ignore Privacy Level Settings" is set in the report settings. This option is designed to make the Firewall ignore security issues such as referring to another query/partition. But the error remains and it seems it remains only for subqueries that use a link specifically  to DataFlow sources. Power BI Desktop refreshes the reports, Power BI portal doesn’t ( the above error occurs).

 

I can indeed rewrite the requests in accordance with the recommendations from here: https://docs.microsoft.com/en-us/power-query/dataprivacyfirewall

 

Perhaps there are other options that do not require refactoring. I tried to enable the option "Allow combining data from multiple sources" in the settings of the Data Flow source, but it does not help...

2 REPLIES 2
AndrewKardenetz
Frequent Visitor

I spent a significant amount of time head-scratching about this same issue. I developed a masterful report locally with dynamic sources, code hotloading, and other cool tricks. It was refreshing just fine on my machine. I had privacy settings all set to none, and privacy checking turned off. Low and behold when I published to the service and tried refreshing there, I got all kinds of failures with error messages that didn't really tell me what was wrong. I spent days reading about formula firewalls both in blogs and the official MSFT documentation, as well as picking apart my powerquery to try and isolate the problems.

At the end, I had to make the hard decision to refactor how each query obtained possibly dynamic information. I had to refactor each query such that it could directly access information between multiple sources. I couldn't pre-load other sources in staging queries.

My solution was to create several user-defined power query functions which controlled details on how to access a data source and retrieve information from that source. Then, for each query, I called those functions to get the info from the source. My non-working version instead referenced a staging query that may have done the same. The working pattern ensures that each query has direct access to the static information about a given data source, and were not referencing other queries to get that information.

 

Another issue you may run into: do not create data source addresses (for example a URL) dynamically at run time. There are specific built-in functions that let you construct content retrieval queries against sites. You will find blogs about this on the net as well.

TLDR: don't confuse static analysis or PowerBI.com refresh will not work!

GilbertQ
Super User
Super User

Hi @gampy 

 

I have found that the only way to get it working is to refactor as per the suggestion in your link.

 

Or what I have done is to then put all the queries into a single table to get it to run.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.