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.

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.

 

6 responses

  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. Pingback: Power BI in Brief – July 2020 - Microsoft Dynamics NAV Community

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

  4. Pingback: Chris Webb's BI Blog: Viewing Data Privacy Partition Information With Power Query Query Diagnostics Chris Webb's BI Blog

Leave a Reply

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

%d bloggers like this: