Data Privacy Settings In Power BI/Power Query, Part 4: Disabling Data Privacy Checks

So far in this series (click here for part 1), I have shown how changing the data privacy settings for a data source can affect the performance of queries and even prevent them from executing completely. What I haven’t mentioned yet is that you also have the option of disabling data privacy checks completely in Power BI Desktop and Excel. In this post I will show you how you can disable data privacy checks and discuss the pros and cons of doing so.

In Power BI Desktop you can change whether data privacy checks are applied when a query executes by going to File/Options And Settings and selecting Options:


The same settings can be found in Excel 2016 by going to the Data tab, clicking Get Data and then selecting Query Options.


In both cases this brings up the Options dialog.

There are two panes in the Options dialog with properties that are relevant to how data privacy checks are applied. First of all, in Global/Privacy, there are global properties that are relevant for every .pbix or Excel file that you open on your PC:


The three options here need a little bit of explanation:

  1. Always combine data according to your Privacy Level settings for each source means that data privacy settings are always applied for every .pbix or Excel file you open, regardless of the properties (described below) that you have saved for individual files.
  2. Combine data according to each file’s Privacy Level settings means that the properties set on individual .pbix or Excel files control how the data privacy checks are applied.
  3. Always ignore Privacy Level settings means that data privacy settings are always ignored, in every .pbix or Excel file you open, regardless of settings saved for individual files.

Then, in the Current File/Privacy pane, there are properties that are saved in and apply to the current .pbix or Excel file that you have open:


The radio buttons here are greyed out if you have options #1 or #3 selected in the previous pane; it’s only if you have selected option #2, Combine data according to each file’s Privacy Level settings, that these properties are taken into account. You may need to close and reopen the Options dialog if you have changed settings in the previous pane but the radio buttons here remain greyed out.

The two options here are:

  1. Combine data according to your Privacy Level settings for each source, which means that the data privacy settings that you have set for each data source are used to control how queries that combine data from multiple data sources behave. This is the default setting.
  2. Ignore the Privacy Levels and potentially improve performance, which means that data privacy settings are completely ignored when queries combine data from multiple data sources.

To sum up, these two groups of properties allow you to choose whether data privacy settings are applied differently for different .pbix or Excel files, or whether, on your PC, they are always applied or always ignored.

For Power BI users it is important to remember that these settings only apply to Power BI Desktop. After a report has been published, if you are using the On-Premises Data Gateway, you also need to configure data privacy settings on the data sources used by your dataset in the Power BI portal. If you are using the On-Premises Data Gateway in Personal Mode (what used to be called the Personal Gateway) then you can configure it to ignore data privacy settings as described here. Unfortunately if you are not using Personal Mode (ie you are using what used to be called the Enterprise Gateway, and what is now just called the On-Premises Data Gateway) then at the time of writing there is no way to configure the gateway to ignore data privacy levels. You can vote here to get this changed. It’s also worth mentioning that right now you can’t combine data from online and on-premises data sources in a gateway either, although it sounds like this limitation will be addressed soon. To work around these limitations you have to import data into separate tables in the dataset and then use DAX calculated tables to combine the data instead – a nasty hack I know, but one that I’ve had to implement myself a few times.

It can be incredibly tempting to avoid the problems associated with data privacy checks by setting Power BI and Excel to ignore them completely. Doing this certainly avoids a lot of headaches and confusion with the Formula.Firewall error message and so on. It also ensures that your queries execute as fast as they can: this is not just because query folding happens whenever possible but because the act of applying the data privacy checks alone can hurt query performance. Recently I saw a case where the only data source used was an Excel workbook (so no query folding was possible) and turning off the data privacy checks made a massive difference to query performance.

However, I cannot recommend that you turn off data privacy checks for all your Excel workbooks and .pbix files by default. Firstly, if you are working with sensitive or highly-regulated data, leaving the data privacy checks in place at least forces you to consider the privacy implications of query folding on a case-by-case basis. On the other hand ignoring data privacy checks by default makes it more likely that you or one of your users will create a query that accidentally sends data to an external data source and breaches your organisation’s rules – or even the law – concerning how this data should be handled. Secondly, if you are a Power BI user and need to use the On-Premises Data Gateway, then you risk creating reports that work fine in Power BI Desktop when the data privacy checks are ignored but which cannot be refreshed after they have been published because the On-Premises Gateway still applies those checks.

In the next part of this series I’ll show how data privacy settings for a data source can be inherited from other data sources.

20 responses

  1. Pingback: #Excel Super Links #88 – shared by David Hager | Excel For You

  2. I am having problems in using Power Query to separate one column has both vendors and its Part No. I am trying to break it up into new column to show only the vendor, Could you give us free lesson in Power Query or use in PowerBI Desktop. Also there an another challenge question in how to figure out the networkDays function in using Power Query or perhaps in PowerBI Desktop. I will appreciate if you help us here. Thanks.  

    Sent: Tuesday, July 04, 2017 at 10:04 AM

  3. Pingback: Lots of Power BI updates and some DAX | Guy in a Cube

  4. if the data source and what’s loaded to power bi cloud are the same, and you’ve got appropriate security at the work group level on the cloud data set, wouldn’t that almost eliminate the risk of “you or one of your users will create a query that accidentally sends data to an external data source” ?


    • I don’t understand the point you’re making here – this isn’t a question of comparing what’s in one data source and what’s loaded to Power BI, it’s to do with what happens when you have two or more data sources.

  5. Doing some research into power query privacy settings and ran into this 5 part series…honestly my head is spinning a bit…
    I might be looking at my answer but still missing it – my question is how to share a workbook containing a PQ query connected to a SQL server database. I, the report writer, have read access to the database, but the report consumer has no access. The workbook is saved in our network file system. What combination of settings would allow the report consumer to refresh the queries, and hence, the workbook?
    Thank you so much for your time,
    – Kurt

    • Hi Kurt, the report consumer also needs access to the database if this scenario is to work. Credentials are never stored in Excel when you use Power Query – this is a deliberate design decision by Microsoft.

      • Thank you for the quick, clear reply Chris. I suppose that makes sense – i’ll pursue another route to get the end user what they need.

  6. Pingback: Data Privacy Settings In Power BI/Power Query, Part 3: The Formula.Firewall Error « Chris Webb's BI Blog

  7. Pingback: Credentials, Data Privacy Settings And Data Sources In Power Query/Power BI « Chris Webb's BI Blog

  8. Pingback: Data Privacy Settings And Data Refresh Performance In Excel And Power BI « Chris Webb's BI Blog

  9. How about when using the PBI Web Service? Is there an option to ignore privacy?

    I have a report that works great in PBI Desktop. The report has multiple functions and there are Formula.Firewall error everywhere. What options do I have to get this report to work using the PBI Web Service?

    • Hi Mike
      Have you tried to move multiple queries into one query as shown by Chris in part 3 of this series?
      I had a report that would not refresh in the service when it was in two parts, but when I re-wrote it to use one query, it has been fine.

  10. I got an xlsm file, which I have a 3 power queries. This file is saved on a network drive.
    xlsm file is password protected and users can only use the file in ready only option.

    Query – 1, is a connection, which Get Data from a network drive from a xlsx file.
    Query – 2, is a connection, which Get Data from a network drive from a xlsx file.

    (both xlsx files are in same folder).

    Query – 3, combine Query 1 & Query 2 and show the data in sheet on my xlsm file.

    I have done almost everything possible, but when any user (other then me) open the xlsm file they get Privacy Setting Error.

    I don’t want to ignore the Privacy level.
    If there is a way to stop other users to get the Error message will be great.
    Or maybe the auto open Macro could select one of the open in the setting.

  11. Hi Chris,

    We create files for clients and save them in a sharepoint folder to be shared among a number of users in an organization. Every time a new user opens the file and saves it, the next user will get the privacy popups. Also, every time I email a file to a new user, they will get the popups.

    The data sources used are:
    Excel input parameters

    Sometimes we have 10 or more Excel inputs and they get a popup for every single data source. These popups render these files unacceptable in the minds of our clients.

    The workaround we’re using is to create a separate file for each user so when they open the file and save the ‘Ignore’ option for the popups, the error won’t come up the next time they open their saved version of the file. This way they only have to cycle through 10 popups once.

    This workaround is acceptable in some situations but not others.

    We’ve tried every possible combination of the data privacy settings but have not come up with a way around this issue.

    Can you help?

  12. Pingback: Analytic Health

Leave a Reply

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

%d bloggers like this: