Excel · Get & Transform · Power BI · Power Query · Uncategorized

Video: Power BI Data Privacy Settings Deep Dive

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.


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.


9 thoughts on “Video: Power BI Data Privacy Settings Deep Dive

  1. @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

  2. 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?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Yes, privacy settings are not saved with the Excel or Power BI file

  3. 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

  4. 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)?.


Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.