3

I have created multiple Excel sheets for my coworkers, many of which involve power query connections to our corporate SharePoint site. When I set it up, I logged into my SharePoint account to authenticate.

The reports refresh for me easily. However, when I send the file to somebody else, and they try to refresh it, they get an "Exception of type 'Microsoft.Mashup.Engine.INterface.ResourceAccessForbiddenException' was thrown" error. They can resolve it by going into power query, data source settings, edit permissions, then choosing "organizational account" instead of "anonymous". once they do that, it works forever.

THe problem is, I have a lot of users. Is there a way to make my queries in such a way that it prompts them to login rather than just throwing an exception? I want them to be able to refresh the reports without having to go into power query, which many of them cannot do without support.

I'm not looking for it to save the password in the file or anything like that - users definitely need to login with their own account, I just want it to remember to use "organizational" authentication instead of "anonymous" by default.

The issue seems to be the same as the one referenced here: https://social.technet.microsoft.com/Forums/en-US/b5ea5742-db99-4507-9844-e4a3a36c67df/excel-power-query-anonymous-credentials?forum=powerquery (where no solution is provided).

Greg Viers
  • 192

1 Answers1

1

There have been hundreds of posts published on the subject, in addition to the one you found. No real solution was ever found, except that of the end-user clearing the permissions from the source, perhaps time and again. This problem is only related to using OneDrive or SharePoint as sources. Even VBA cannot delete these credentials.

Some "solutions" are listed in the YouTube video How to use Power Query to connect to a file on OneDrive or SharePoint, but each one is more complicated than the previous, so are totally unsuitable for end-users.

I have done some research, but ended up with only these two solutions that I see as viable:

  • The solution you already found of clearing the permissions.
  • Export the data to a network share, perhaps in .csv format, and give all users access to it.

The second solution avoids the headache of credentials, but lacks the technological sophistication of the first one.

harrymc
  • 498,455