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
  • Any updates on the sub-site navigation?
    Wednesday, October 08, 2014 7:20 PM
  • FYI - I was able to get around sub-site navigation with the SP List connection by using "From Web" and putting the direct url for the SP List.

    https://rootsite.sharepoint.com/teams/subsitename/Lists/ListName/AllItems.aspx

    Thursday, October 09, 2014 11:17 PM
  • However, this don't work with calendar list, as an example. If this is the case, you just receive one entry for "Document" :(

    Dani Seara

    Tuesday, October 14, 2014 3:00 PM
  • It is very BAD that STILL at this time, in XXI century, components and services do not work "at least you use it in English"... Very bad attitude :(
    I discuss this already with several teams in Corp. and seems like it is time (finally) to declare some kind of test rule. if you do not test the product in several languages, you DO NOT release it.
    My dos centavos

    Dani Seara

    Tuesday, October 14, 2014 3:02 PM
  • I assure you that a huge portion of our time and effort on Power Query is spent in localizing and globalizing the product and then testing in each of these languages. Release are not allowed out the door unless they meet the bar for each of these languages. Power Query currently supports over 40 languages, but obviously there are bugs that slip through, and we are thankful when you report them!
    Tuesday, October 14, 2014 3:17 PM
    Moderator
  • I assure you that a huge portion of our time and effort on Power Query is spent in localizing and globalizing the product and then testing in each of these languages. Release are not allowed out the door unless they meet the bar for each of these languages. Power Query currently supports over 40 languages, but obviously there are bugs that slip through, and we are thankful when you report them!

    Hi Ben,

    Do you have an estimate when this annoying bug Will be fixed? My Sharepoint sites have the dutch Language as standard and have the same issue. Would Be great to Be able to connect to it without work arounds.

    Kind regards,

    Jacco


    • Edited by Jacco2015 Sunday, November 09, 2014 5:13 PM
    Sunday, November 09, 2014 5:12 PM
  • Hi Curt

    After 10 Months... is there a solution for the localisation issue in order to connect to SharePoint lists?

    thanks, ama


    ama

    Wednesday, January 14, 2015 10:48 AM
  • Hi Ama,

    Unfortunately we're still having difficulty mapping the existing experience for English lists to localized lists. We are still investigating. These sites can still be connected to directly through the OData function in Power Query. We have seen a newer version of the SharePoint API which may help, but we don't have any timelines on a complete solution yet.

    Thanks,

    Theresa

    Wednesday, January 14, 2015 4:48 PM
    Owner
  • Hi Theresa

    Thanks for the quick reply.

    I really don't understand, why this takes so Long...

    Is there a clear description on how to connect by OData?

    Will this be a sustainable solution or will we need to Change all Connections in some months again, when you Change the product?

    Best regards, ama


    ama

    Thursday, January 15, 2015 8:12 AM
  • The reason this is hard to fix is that the code we wrote makes the assumption that the programmatic SharePoint API returns a relatively-stable OData schema. It did not remotely occur to us that the schema returned by SharePoint might depend on the language of the installation -- arguably, this is not a good practice for API design and I believe the newer SharePoint 2013 API does not have the same behavior. Without a stable schema, there's no reliable way for us to infer the right information about the results we get back from the service.
    Thursday, January 15, 2015 3:55 PM
    Moderator