Forum Discussion
Getting data from on-prem sharepoint into Power BI Desktop
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
Hi Mike,
I would like you to try only the site url not the list URL. In PowerBI Desktop, when you use from SharePoint List, enter the site url then use windows credentials. Meanwhile, run fiddler on the side and enable HTTPS in fiddler (Tools --> Fiddler Options --> HTTPS --> Check the check box for decrypt HTTPS traffic).
When you get that fiddler trace, select all related requests and click save as archive. (I would really appreciate if you can click send a frown and send us the fiddler trace :) ) but if the data is sensitive, here is something else:
In fiddler, the request that returned 400, right click on it and select copy just URL. Paste that URL in the browser and see if it will work or not. Also, what's the URL that returns 400? Is it the $metadata? Or the list?
Are you able to see all your lists before clicking on one of them? Does the error happen when you click on a list or even before that?
Thanks,
Hadeel
Hi!
>>I would like you to try only the site url not the list URL
I did both for fun ;) Both error'd out.
>>Get that fiddler trace ... click send a frown and send us the fiddler trace
Done and Done!
>>what's the URL that returns 400? Is it the $metadata? Or the list?
If you mean 404, yes it's the $metadata.
>>Are you able to see all your lists before clicking on one of them? ... or even before that?
No - the error happens before that - just after submitting the list URL
Thanks!
-Mike