Home

Excel Power Query Refresh fails for a shared file owner on SharePoint using Office 365 Business Prem

%3CLINGO-SUB%20id%3D%22lingo-sub-483053%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20Refresh%20fails%20for%20a%20shared%20file%20owner%20on%20SharePoint%20using%20Office%20365%20Business%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483053%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere'%20the%20general%20flow%3A%3C%2FP%3E%3CP%3E1a)%20SharePoint.Files(%22%3CA%20href%3D%22https%3A%2F%2Fmydomain.sharepoint.com%2Fsites%2FCommissions%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmydomain.sharepoint.com%2Fsites%2FCommissions%2F%3C%2FA%3E%22%2C%20%5BApiVersion%20%3D%20%22Auto%22%5D))%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E1b)%20SharePoint.Files(%22%3CA%20href%3D%22https%3A%2F%2Fmydomain.sharepoint.com%2Fsites%2FCommissions%2Fmyfilename.xlsx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmydomain.sharepoint.com%2Fsites%2FCommissions%2Fmyfilename.xlsx%3C%2FA%3E%22)%3C%2FP%3E%3CP%3E2)%20Filter%20files%20by%20name%20(for%20option%201a%20above)%3C%2FP%3E%3CP%3E3)%20GetExcelObjects%20(filter%20by%20kind%3Dsheets)%3C%2FP%3E%3CP%3E4)%20Expand%20tables%3C%2FP%3E%3CP%3E5)%20...manipulate%20data%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483015%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20Refresh%20fails%20for%20a%20shared%20file%20owner%20on%20SharePoint%20using%20Office%20365%20Business%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327694%22%20target%3D%22_blank%22%3E%40SAI-Indy%3C%2FA%3E%26nbsp%3B%2C%20how%20exactly%20do%20you%20reference%20files%20on%20SPO%3F%20Correct%20query%20is%20like%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESharepoint.Files(%3CROOT%20site%3D%22%22%20url%3D%22%22%3E)%26nbsp%3B%3C%2FROOT%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EFiltrer%20file%20by%20name%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EGet%20binary%20content%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EGet%20object%20from%20file%20(table%20or%20sheet)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-486401%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20Refresh%20fails%20for%20a%20shared%20file%20owner%20on%20SharePoint%20using%20Office%20365%20Business%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-486401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327694%22%20target%3D%22_blank%22%3E%40SAI-Indy%3C%2FA%3E%26nbsp%3B%2C%20never%20seen%20any%20problems%20with%201a%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%201b%20shall%20not%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482973%22%20slang%3D%22en-US%22%3EExcel%20Power%20Query%20Refresh%20fails%20for%20a%20shared%20file%20owner%20on%20SharePoint%20using%20Office%20365%20Business%20Prem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482973%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20recently%20generated%20several%20Excel%20spreadsheets%20on%20our%20office%20365%20sharepoint%20drive.%26nbsp%3B%26nbsp%3BI%20use%20Power%20Query%20in%20Excel%20to%20manipulate%20data%20and%20generate%26nbsp%3BPivot%20reports%20in%20the%20excel%20spreadsheets.%26nbsp%3B%20All%20references%20to%20the%20excel%20spreadheets%20use%20web%20links%20and%20not%20local%20drive%20directories.%26nbsp%3B%20All%20of%20the%20files%20have%20been%20shared%20with%20a%20co-worker.%26nbsp%3B%20While%20the%20co-worker%20can%20sync%20the%20files%20locally%20to%20their%20machine%20and%20open%20the%20files%2C%20they%20cannot%20refresh%20the%20data.%26nbsp%3B%20My%20co-worker%20receives%20the%20following%20error%3A%20%5BException%20of%20type%20%22Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException%22%20was%20thrown%5D.%26nbsp%3B%20We%20are%20both%20using%20our%20Organization%20Account%20credentials%20to%20access%20the%20data%20source.%26nbsp%3B%20I%20suspect%20that%20this%20is%20where%20the%20issue%20is%20originating%2C%20but%20looking%20for%20a%20workaround%2C%20since%20we%20both%20need%20to%20access%2C%20edit%20and%20refresh%20the%20excel%20files%20and%20the%20corresponding%20power%20queries.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482973%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779596%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20Refresh%20fails%20for%20a%20shared%20file%20owner%20on%20SharePoint%20using%20Office%20365%20Business%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20generalised%20step-by-step%20guide%20for%20how%20to%20share%20power%20query%20enabled%20Excel%20files%2C%20without%20hitting%20this%20error%2C%20please.%20I%20have%20the%20same%20error%20(with%20two%20files%20and%20do%20not%20understand%20a)%20why%20it's%20there%20or%20b)%20why%20some%20users%20can%20refresh%2C%20while%20others%20cannot%20or%20c)%20what%20to%20do%20to%20fix%20it.%20Any%20help%20gratefully%20received%2C%20please.%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E
SAI-Indy
New Contributor

I have recently generated several Excel spreadsheets on our office 365 sharepoint drive.  I use Power Query in Excel to manipulate data and generate Pivot reports in the excel spreadsheets.  All references to the excel spreadheets use web links and not local drive directories.  All of the files have been shared with a co-worker.  While the co-worker can sync the files locally to their machine and open the files, they cannot refresh the data.  My co-worker receives the following error: [Exception of type "Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException" was thrown].  We are both using our Organization Account credentials to access the data source.  I suspect that this is where the issue is originating, but looking for a workaround, since we both need to access, edit and refresh the excel files and the corresponding power queries.

4 Replies

@SAI-Indy , how exactly do you reference files on SPO? Correct query is like

 

Sharepoint.Files(<root site URL>) 
Filtrer file by name 
Get binary content 
Get object from file (table or sheet)

@Sergei Baklan 

 

Here' the general flow:

1a) SharePoint.Files("https://mydomain.sharepoint.com/sites/Commissions/", [ApiVersion = "Auto"]))

or

1b) SharePoint.Files("https://mydomain.sharepoint.com/sites/Commissions/myfilename.xlsx")

2) Filter files by name (for option 1a above)

3) GetExcelObjects (filter by kind=sheets)

4) Expand tables

5) ...manipulate data

@SAI-Indy , never seen any problems with 1a

 

IMHO, 1b shall not work

@Sergei Baklan 

Is there a generalised step-by-step guide for how to share power query enabled Excel files, without hitting this error, please. I have the same error (with two files and do not understand a) why it's there or b) why some users can refresh, while others cannot or c) what to do to fix it. Any help gratefully received, please.

Thanks,

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies