Over the past year or so I’ve been delivering a presentation on the Power Query engine’s data privacy settings at various conferences and Power BI user groups, in an attempt to try to pull together all the knowledge I have on this complex topic. Luckily, when I presented this session at the London Power BI User Group recently, they recorded it and posted it on YouTube here:
If you’re struggling with data privacy errors like:
Formula.Firewall: Query ‘Query1’ (step ‘xyz’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
or
Formula.Firewall: Query ‘Query1’ (step ‘xyz’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
…in either the Power Query Editor in Power BI or Power Query/Get&Transform in Excel, then I hope this video will help you understand why you’re getting these errors and what you can do to avoid them.
A lot of what I show in this session draws on other material, such as:
- My five-part series of posts on the Power Query data privacy settings that starts here
- My post on how credentials and data privacy settings are stored for dynamic data sources here
- My post here on the performance overhead of applying data privacy checks
- Ehren von Lehe’s detailed paper on (available here) on how the engine partitions data sources while applying data privacy checks. One of the things I show in the video is that it’s now possible to see these partitions using Power Query Query Diagnostics (see here for some details – although I should probably devote a whole blog post to this in the future)
Lastly, one minor correction to something I said in the video: at the 44:32 mark I do a demo that shows how you can use M functions to avoid a Formula Firewall error. While this trick works in Power BI Desktop and Excel, it results in a dataset that can’t be refreshed in the Power BI Service unfortunately.
@Chirs – The issue you mention @44:32 – of passing a File Path stored in a table to another Query does not work in the service (for scheduled refresh) even when the Privacy setting is set to Ignore, Correct ?
So the only way to get it working in the service is to build the query using Parameters rather that to read from a parameter table
hi Chris, as you said in Excel query is working the same way, maybe the issue I have there could be pointed to privacy levels as well. I set up privacy and credentials to “organizational” and my file works perfectly. But when other users opens this template, privacy goes to “None” and credentials to “Anonymous” without even asking users to choose anything. Then the queries pulling data from another file on SharePoint fails and gives lots of errors creating big confusion.
So how could it be that these settings are not retained in the file and works differently from user to user?
Yes, privacy settings are not saved with the Excel or Power BI file
So I have no way to make a template which would ask to choose the privacy level every time a new user opens it?
Hi Chris and thanks for a (as usual) very good explanation of this. As I still struggled (and you as well I could her in the video), to fully understand this, I also red the article by Ehren. It’s a very good article, and it was even so good that I finally understood what’s wrong, and this time I think we have some (maybe not bugs but…) lack of perfections in the power query formula engine that if fixed, both could fix some unnecessary formula engine errors, but also speed up many queries where the formula engine probably steps in it’s checks by no reason.
Since you probably will have some influences in this at Microsoft, please read through my comment (by some reason my name is: erapade in the article): https://social.technet.microsoft.com/Forums/en-US/ca434e2d-88fe-4962-b46a-a1db51e8bd89/feedback-wanted-behind-the-scenes-of-the-data-privacy-firewall
Hi Chris,
I’m just wondering. Can a data source with a privacy level set to ‘Public’ pass on information to a data source with a privacy level set to ‘None’ (assuming that it hasn’t inherited another privacy level)?.
Thanks