none
Power Query error when connecting to Office 365 SharePoint site

    Question

  • I would like to make Power Query for SharePoint list from Power BI site.

    Power Query->From Other Sources->From SharePoint List->

    URL= https://powerbisampledev-my.sharepoint.com

    ERROR IS

    DataFormat.Error: OData: The given URL neither points to an OData service or a feed: 'https://login.microsoftonline.com/login.srf?wa=wsignin1.0&rpsnv=2&ct=1382593953&rver=6.1.6206.0&wp=MBI&wreply=https://powerbisampledev-my.sharepoint.com/_forms/default.aspx&lc=1033&id=500046&guests=1'. Details: https://login.microsoftonline.com/login.srf?wa=wsignin1.0&rpsnv=...


    Kenny_I

    Thursday, October 24, 2013 5:58 AM

Answers

  • Which credentials did you give it? I get that error if I try to use Anonymous creds for one of my SharePoint online sites. To change your credentials for a datasource that you've already connected to:

    1. In the Power Query tab in Excel, click Data Source Settings (in the Machine Settings group.)
    2. Scroll to the entry for your SharePoint site and select that row.
    3. Near the top of the dialog, select Edit Credential.
    4. I'm guessing you want the Microsoft Online Services ID auth option. Choose that and then read/follow the instructions to sign in (and stay signed in.)
    Saturday, October 26, 2013 3:30 AM
    Moderator

All replies

  • Which credentials did you give it? I get that error if I try to use Anonymous creds for one of my SharePoint online sites. To change your credentials for a datasource that you've already connected to:

    1. In the Power Query tab in Excel, click Data Source Settings (in the Machine Settings group.)
    2. Scroll to the entry for your SharePoint site and select that row.
    3. Near the top of the dialog, select Edit Credential.
    4. I'm guessing you want the Microsoft Online Services ID auth option. Choose that and then read/follow the instructions to sign in (and stay signed in.)
    Saturday, October 26, 2013 3:30 AM
    Moderator
  • Thanks Ben, I had the same problem and I think I had mistyped the password.

    Now I connect to the O365 SharePoint site, but the list of lists is empty in the Power Query window, in the navigator pane. Ever heard of that?

    Tuesday, October 29, 2013 10:42 AM
  • Hi Florian,

    We have not seen this behaviour before. Do you have any permission restrictions on the SharePoint lists you're expecting to see? We have had feedback around difficulty accessing lists on SharePoint subpages, are the lists on the base site or on the subpages? Any other information you have, such as the URL to a list you're looking for, could help us identify the issue.

    Thanks,

    Theresa

    Friday, November 01, 2013 6:00 PM
    Owner
  • I have the exact same problem as Florian. I'm able to authenticate to the SharePoint Online site. The Query Editor states that it's downloading, but the Navigator does not show anything. I've tried multiple SharePoint online instances, but none of them seems to work. I've also installed Power Query on the laptops of my co-workers, but they experience the exact same problems. We're running 64 bit Windows 8 with a 32 bit Office 2013 Professional Plus installation.
    Wednesday, November 06, 2013 2:54 PM
  • Theresa,

    I'm using SharePoint on Office 365. I'm administrator of our Office 365.

    I have the problem on SharePoint 2010 on my dedicated space (https://biosgroup1-my.sharepoint.com) where I have created a list at the root level (but you will know better if it's a site or subsite as I'm not really versed on SharePoint be it or not on Office 365).

    I have the same problem on SharePoint 2013 on our regular space (https://biosgroup1.sharepoint.com/), where I have also created a list at the root level.

    I'm on Power Query 1.5.3296.1161 - Excel 2013 64bit 15.0.4535.1507

    Wednesday, November 06, 2013 4:14 PM
  • Thank you both for your information - we have received similar feedback and have filed a bug. We are currently investigating and I will provide updates when available.

    Thanks,

    Theresa

    Thursday, November 07, 2013 6:04 PM
    Owner
  • Hi,

    I had the same problem as Florian - I couldn't get data from SharePoint lists with Power Query.

    Authentication works but Power Query does not display any content for the SharePoint site.

    I'm using SharePoint 2013 for Office 365 and Power Query 2.10.3547.461 32 bits.

    The SharePoint site is in French and the problem was solved by creating a subsite in English and defining lists in that subsite.

    So at least in my case, this appears to be a localization thing.

    Tuesday, February 11, 2014 8:52 AM
  • Hi Jordan,

    Unfortunately there is a known localization issue with our SharePoint connector. We expect certain values to be returned and cannot identify those values when the feed is localized, leading the product to believe there are no lists.

    Your solution worked as the English site returned the expected fields. Another workaround would be to use our OData connector directly, with the ListData.svc URL instead of the site URL. Unfortunately the experience isn't tailored for SharePoint the way our SharePoint List connector is, but it can offer a short term solution.

    We're continuing to investigate ways to improve the experience in our SharePoint connector for localized sites. Please let us know if you have any other issues.

    Thanks,

    Theresa

    Tuesday, February 11, 2014 4:42 PM
    Owner
  • The list that I am attempting to connect to is levels of sub-sites below the top site. When I supply the sub-site path, it gives me: DataFormat.Error: OData: The given URL neither points to an OData service or a feed:

    When I supply the root site

    (using the recommendation: When connecting to a SharePoint list, enter the site URL instead of the list URL. In the Access SharePointdialog box, select the most general URL to authenticate against the site correctly. By default, the most general URL is selected.)

    it works, but I am not able to navigate down to my list on my sub-site. Is there a different way to navigate to my sub-site??

    Saturday, March 08, 2014 12:54 AM
  • Hi Theresa

    Are there any news? Have you been able to solve the issues with the SharePoint connector? Still not able to connect.

    Thanks


    ama

    Wednesday, May 21, 2014 12:02 PM
  • is there any update?

    ama

    Wednesday, May 21, 2014 12:14 PM
  • If you're asking about the ability of Power Query to read from SharePoint sites whose default language is something other than English, then no, there's no news.
    Wednesday, May 21, 2014 1:27 PM
    Moderator