Forum Discussion

TusharGaurav's avatar
TusharGaurav
Helper III
8 months ago
Solved

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 ...
Need Help
  • TusharGaurav's avatar
    TusharGaurav
    8 months ago

    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

rohit1991's avatar
rohit1991
Super User
8 months ago

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.

TusharGaurav's avatar
TusharGaurav
Helper III
8 months ago

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

  • rohit1991's avatar
    rohit1991
    Super User
    8 months ago

    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.

  • TusharGaurav's avatar
    TusharGaurav
    Helper III
    8 months ago

    Hi Rohit,

     

    Thanks for your help.

    Its working.

     

    Thanks, 

    Tushar