I have been searching the internet quite extensively but couldn't find the answer I am looking for. Hope somebody here can help me.
I have a company Sharepoint (Office 365) where I synchronize Excel workbooks via OneDrive on a monthly basis. These Excel workbooks are forming the basis of a Power Query that several employees have sitting in an Excel workbook on their local machine. Now the idea is, that every employee can refresh the data in their local Excel workbook at any given time. While the whole stretch is working in general, I have hit a major usability issue. When trying to refresh the Power Query I (and any other user within the company) gets the error message: Exception of type 'Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException' was thrown. I can work around that error by going through the painful stretch of:
- Excel menu - Query - Edit
- Power Query menu - Home - Data Source Settings
- Enable Radio button Data sources in current workbook, select the data source and press Edit Permissions
- Delete the current Credentials
- Edit current credentials, select Organizational account, press Sign in
- When asked Pick an account select the one shown (which will be the one of the user)
- Press Save, press OK, press Close
- Power Query menu - Home - Close & Load
- Back in the Excel worksheet right mouse click refresh and it works a charm! But you will agree that this is not feasible from a user's perspective.
Now I was hoping to find a switch where I could change that to permanently working or as a programming solution, supply a Refresh button that then does the trick. Unfortunately so far I failed with both of my ideas.
Can anybody point me in the right direction here? Any help is much appreciated! Thx a lot!
Regards
