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 this line of VBA code in the comments on the following 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.