Power Query - Difference Permissions for a column that doesn't exist

Question

I am working in Excel2016 and have produced a PowerQuery to get information from a database, this server/database is populated from a named field in the workbook, when running on some machines (various machines Excel development is taking place on, over multiple accounts) this executes fine, however the QA machines are having the following error.

"Query 'TaskIdProc' (step'AutoRemovedColumns1') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination"

The code being used is as follows

let
    ServerName = Text.From(Excel.CurrentWorkbook(){[Name="ServerName"]}[Content]{0}[Column1]),
    DatabaseName = Text.From(Excel.CurrentWorkbook(){[Name="DatabaseName"]}[Content]{0}[Column1]), 
    TaskID = Text.From(Excel.CurrentWorkbook(){[Name="TaskName"]}[Content]{0}[Column1]),
    Source  = Sql.Database(ServerName,DatabaseName,
                   [Query="EXECUTE dbo.bsp_DeadlineHistoryByTaskID
                       @Task_ID_Param ='" & TaskID & "'"
               ]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"TaskGuid", "Task_ID", "InitialDeadline"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewDeadline", "New Deadline"}, {"RequestedDeadline", "Requested Deadline"}, {"NewState", "State"}, {"ActionTaken", "Action"}, {"ReasonGiven", "Reason"}, {"UserName", "Who"}, {"ChangeTimeStamp", "When"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"New Deadline", "Difference", "State", "Action", "Reason", "Comment", "Who", "When", "Requested Deadline"})

in
    #"Reordered Columns"

As you can see, the step in the error message does not exist in the query, and I have now hit a brick wall. I have tried ensuring that only one data source is used per line. Passing this to QA with all data connections being cleared. Ensuring that the step causing a problem does not exist.

This is Excel 2016, requiring backwards compatibility for 2013.

Alejandro Lopez-Lago - MSFT Alejandro Lopez-Lago - MSFT · Accepted Answer · 2017-04-04T20:11:50

That error means that the step is using two sources with incompatible privacy levels together. For example, if your current workbook is marked as Private but your SQL database was marked as Organizational, then the evaluation would be blocked because you are sending information from a private data source to a less sensitive data source (the database).

There are a couple possibilities of why you see it on some machines and not others:

Privacy levels on the machines where it works are disabled globally, while privacy levels on the QA machines are still enabled.
The privacy levels for Current Workbook and for the SQL database are different between the QA machines and the other ones.

You can check if it is the first case by going to Options, clicking on the Privacy under the Global header and checking what is chosen under Privacy Levels.

For the second case, you can check the privacy levels of specific sources by going to Data source settings, selecting the data source, and clicking on Edit Permissions.

Power Query - Difference Permissions for a column that doesn't exist

1 Answers