Forum Discussion
Not able to refresh a semantic model having excel as a source kept in sharepoint
Hi Experts,
In the power bi report that I developed, I am using a excel file kept at a sharepoint location.
I am able to connect to sharepoint from power bi desktop.
However when I publish the report to power bi service and try to refresh the semantic model I am getting connection error as below:
| Data source error: | {"error":{"code":"DMTS_OAuthFailedToGetResourceIdError","pbi.error":{"code":"DMTS_OAuthFailedToGetResourceIdError","details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Failed to get OAuth resource, please make sure the OAuth is supported"}}],"exceptionCulprit":1}}} |
Can you please help me in establishing the connection to sharepoint from power bi service.
Thanks and Regards,
Tushar Gaurav
Hi Rohit,
Thanks for your response.
I was using web connector to connect to the file with url similar to below url:
https://xxxx.com/:x:/r/sites/xxxx/Shared%20Documents/xxxx/xxxx/abc.xlsx
However I chnaged the connector type to "SharePoint Folder" and used below URL:
https://xxxx.com/:x:/r/sites/xxxx/Shared%20Documents/xxxx/xxxx
But now I am not able to connect from power bi desktop too and getting the below error:
Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"Please suggest.
Thanks,
Tushar
5 Replies
Hii TusharGaurav
This issue occurs because Power BI Service cannot authenticate to the Excel file stored in SharePoint using the credentials from Power BI Desktop. After publishing, you must reconfigure the data source credentials in Power BI Service. Go to Dataset (Semantic model) >> Settings >> Data source credentials, edit the SharePoint source, and sign in using OAuth2 (Organizational account) with access to the file. Also ensure the SharePoint URL is the site URL (not the file URL). No on-premises gateway is required for SharePoint Online.
Hi Rohit,
Thanks for your response.
I was using web connector to connect to the file with url similar to below url:
https://xxxx.com/:x:/r/sites/xxxx/Shared%20Documents/xxxx/xxxx/abc.xlsx
However I chnaged the connector type to "SharePoint Folder" and used below URL:
https://xxxx.com/:x:/r/sites/xxxx/Shared%20Documents/xxxx/xxxx
But now I am not able to connect from power bi desktop too and getting the below error:
Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"Please suggest.
Thanks,
Tushar
Hii TusharGaurav
The 404 error occurs because the SharePoint Folder connector expects the site or document library URL, not the file path. Use the SharePoint site URL only, for example:
https://xxx.com/sites/xxxx
Then, in Power Query, filter the returned list to the required Folder Path / Name to select abc.xlsx. Do not include /Shared Documents/.../file.xlsx in the connector URL. Also make sure you sign in using Organizational account (OAuth2). This approach works in both Power BI Desktop and Service for scheduled refresh.
Get the File URL: Go to the Excel file in your OneDrive for Business via your web browser.
Click "..." (More options), then "Details." Scroll down to the bottom of the pane that appears and copy the file path by clicking "Copy Path."
Import in Power BI Desktop:
Go to Get Data --> Web.
Paste the modified URL.
Power BI will ask for credentials. Choose "Organizational account" and sign in.
