Solved: Getting data from on-prem sharepoint into Power BI... - Page 2

stefam · ‎08-28-2015

Hello,

Moving forward with Power BI desktop, I am able to connect our on-prem Sharepoint 2013 lists to an excel spreadsheet and then connect the spreadsheet to Power BI Desktop. I cannot connect directly to the Sharepoint list via the Sharepoint Connector or through OData. The OData issue seems to be a bug (see https://social.technet.microsoft.com/Forums/en-US/63c57fd7-109a-4a2c-82a7-409494f19303/trouble-getti...), but the Sharepoint connector may also be a bug. I can't find anyone on the forum with this exact issue, so please help if you can:

For search purposes, the error is Cannot find resource for the request $metadata

I am using Windows Security to authenticate.

Thanks,

Mike

pnussbaumer · ‎09-17-2015

Okey, we have found the solution. Thanks to our internal pros! 🙂 We had the following behavior:

- Some URLs (SharePoint Site) worked, others not.

- With Fiddler we found out that the URL for the Webservice, which Power BI Deskotp calls in the background, are not always the same. Example: https://example.url.com/spsite1 called the Webservice /spsite1/_vti_bin/ListData.svc/$metadata and the other URL https://example.url.com/spsite2 called the Webserive /spsite2/_api/web/$metadata.

- The "spsite1" worked, "spsite2" not.
- We found out that one site was based on a upgraded SharePoint 2010 site template and the the other based on a SharePoint 2013 site template. So, thats the reason for the different calls of webservice URLs (old and new). The "old" one worked, the "new" one not.
- We found a workaround. Now all works fine. Go to the SharePoint Server and to the "_vti_pvt"-folder in your "inetpub" of IIS. Then look for the file "service.cnf" and delete it - or better rename it. Power BI takes the old OData service when he can not find this file. That's it.

Hope it helps as a workaround.

philip

View solution in original post

Getting data from on-prem sharepoint into Power BI Desktop

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024