Power Query Privacy Setting Problem

Excel 2016 solution

Privacy settings allow you to control who sees the Power Query data. There seems to be a bug that remembers your response to a dialog and this ignores any changes to the Privacy settings. Find out the VBA line of code that can fix it.

In the Power Query settings you can set the Global and the Workbook Privacy settings, see both images below.

WARNING: This solution may not be applicable to all cases. For data used at an organisational level it can work.

If you have incorrectly responded to the Privacy dialog (see below) that opens when you initially run a Query then that response is remembered.

That initial response can stop you from running the Query again. Even if you change the Query settings to ignore the Privacy levels it doesn’t help.

The error message that displays is

This happened to a client of mine recently and luckily I found a line of VBA code in the comments on an online article.

It only works with Excel 2016. The line of code is

ThisWorkbook.Queries.FastCombine = True

I added this to the Workbook Open event and when I changed the Privacy settings to reduce Privacy levels this solved the issue.

The setting it changes is also a Query setting – see image below.

Hopefully this issue will be fixed in a future update.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

2 thoughts on “Power Query Privacy Setting Problem

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

    • As I mentioned in the post I used the open macro with a line of code that seemed to fix it.

      If you aren’t willing to change the privacy levels I am not sure if there is a workaround.

      I am not sure what the privacy level does in an organisational setting.

      The folder access restrictions should be enough to protect the data.

      If they have access to the folder they get the data – if they don’t have access they can’t get it.

      Maybe test with someone who doesn’t have access and see if they get access.

      Sorry if that wasn’t helpful.