Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stefam
Frequent Visitor

Getting data from on-prem sharepoint into Power BI Desktop

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:

Error.PNG

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

 

I am using Windows Security to authenticate.

Thanks,

Mike

1 ACCEPTED SOLUTION

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

17 REPLIES 17
pnussbaumer
Frequent Visitor

Hello,

 

I have the same issue since days. Did you have found a solution for this? I tried many things, also the thing with the column name.

 

Thanks for an answer in advance, philip

We did stumble upon a solution!  I hope it works for you...

 

When connecting to the SharePoint site, we dat to do two things:

  1. Authenticate with your Windows: Alternate Credentials AND
  2. When selecting which URL to apply the settings to, you must select the bottom one – specific to the subsite

Error.JPG

 

Then we were able to connect to the site and see all the lists an the site!

 

I am now able to select and connect to all the 10-15 lists on the site except, of course, the one which I need.

 

But that is another issue...

 

Mike

Hi @stefam, I connect the same way to SharePoint lists using alternate windows credentials for Odata feed.

In Power BI desktop it works fine, I can see all the lists and create reports as well as refresh data. Than I publish it to Powerbi.com service. I want to schedule refresh. How did you achieve this? As In service it is not possible to setup refresh using alternate windows credentials.

 

Thx

Sorry, Can't help there - We don't publish to PowerBI.com - It is not currently secured for the type of data we work with.  Hoping to use it when it gets implemented in MAG, but I don't have an answer for your question today.

 

Mike

Mike, thanks very much for your help. But the thing is that I don't have the option to give the credentials to the connection. So first this screen:
2.PNG

Then I press "Ok" and after one second it appears that screen:
3.PNG

 

Is there a possibility to apply to that URL credentials?

Thanks again.

philip

Weird.  Try File->Options and Settings->Data Source Settings  and then delete that data source. 

 

Then try reconnecting to the SharePoint source - it should then ask you for a credential.

 

Mike 

We have the same issue.  Note that this DID not happen with the Power BI Desktop Preview from April 2015.  So the secondary check for metadata must be new for the released version.

 

See this thread as it think its the same problem:

https://social.technet.microsoft.com/Forums/en-US/63c57fd7-109a-4a2c-82a7-409494f19303/trouble-getti...

 

Scheduling a Refresh aslo errors out saying that the credentials are bad, but i suspect its related to this same issue where the metadata cannot be downloaded.

 

 

Hi

 

Have you tried to connect via a Web data connection and then applying the url to the listview "Allitems" like this

 

http://<yoursite>/<subsite>/<listname>/allitems.aspx

 

regards

Per Jeilsoe

@Jeilsoe I was actually getting a different error, "Internal Server Error (500)" trying to use the Sharepoint list and oData sources for my list, but this worked for me.

 

Thanks!

Wally West 

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

I changed the _vti_pvt folder's name and the name of the files inside the folder. after that reset the app in IIS and i still get the following error Details: "SharePoint: Request failed: The remote server returned an error: (400) Bad Request. (Bad Request)"

Hi,

 

I am having the same error with my sharepoint URL. I am using sharepoint 2010 and want it to be directly connected to Power BI but unable to do so as it is throwing error.

 

How we can go to sharepoint server to make the change you have notes in your post. I am quite new to sharepoint so need your guidance.

 

Shivjeet Deoghare

Thanks, it works for me!!!, now the problem is that in some sites appear as not authorized user and others connect perfectly (I'm a full control user in all sites), so I don't know what could be happening in these sites that my user appear as a not authorized user. Any ideas??

Greg_Deckler
Super User
Super User

Looks like you have run into the "column starts with a number" bug. This was a discussion I participated in on Technet a while ago. Basically, if you had a SharePoint list in your site that had a column that began with a number, this would happen. Drove me crazy for a bit. Renaming column fixed the issue. I'll try to dig up the article link.

 

@stefam Found the thread:

https://social.technet.microsoft.com/Forums/en-US/8728909b-f06f-404f-b6af-bb49802959c5/i-found-a-bug...

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you for the suggestion - I tried looking at all the lists to avoid numbers in the column names or list names.  I even created a brand-new team-site with the default lists from Sharepoint 2013.  I still get the same error.

I am connecting with Windows Authentication and also tried Anonymous. I also tried connecting at site and sub-site levels. 

 

If I connect directly to the list instead of the site, I get The given URL neither points to an OData Service or a feed.

 

I can see the list if I connect to the data as a if I'm importing from a webpage (Get Data->Web), but I need to get at the table data, not the view.

 

Other ideas?

-Mike

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

stefam
Frequent Visitor

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 Smiley Happy

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors