none
Power Query cannot pull lists from SharePoint subsites

    Question

  • I have been able to use Power Query to pull lists from root sites in SharePoint, but it will not return anything from any subsites.  Can anyone point me in the right direction?
    Monday, August 19, 2013 4:56 PM

Answers

All replies

  • Hi gitmox,

    Have you tried entering the root of the subsite, something like, https://mysite/subsite/, into the Power Query builder? Connecting to https://mysite for example, would only return the lists on that site. By specifying the subsite URL you should be able to access those lists as well. Please let us know if this solves your issue.

    Thanks,

    Theresa

    Friday, August 23, 2013 3:27 PM
    Owner
  • I work with Gitmox...

    Let me clarify with the following examples:

    https://mainsite/ - works

    https://mainsite/news - works (subsite off root)

    https://mainsite/sites/finance - works  (separate site collection)

    https://mainsite/sites/finance/accounting  - ERROR

    Error:  "DataSourceError:  OData:  The feeds metadata document appears to be invalid.  Details: null"

    So it doesn't seem to work with subsites in separate site collections...  which, of course, is where we're trying to pull the data from.

    Thursday, August 29, 2013 2:33 PM
  • I'm experiencing similar problem with the Power Query Preview. When I paste in a valid URL, specifying Windows Credentials, I get DataSource.Error: OData: Request failed (ProtocolError): The remote server returned an error: (400) Bad Request.  There is not really anything I can do with that error.

    Thursday, August 29, 2013 9:23 PM
  • Hi everyone,

    Thanks for reporting these issues. We're looking into this and will reply back with our findings, or to get more info if needed.

    Regards,
    M.

    Wednesday, September 04, 2013 6:39 PM
    Owner
  • I also running to same issue.
    Friday, November 08, 2013 12:14 AM
  • I am also running into same issue. Below two Power queries works fine

    = SharePoint.Tables("https://xxx.sharepoint.com")

    = SharePoint.Tables("https://xxx-my.sharepoint.com/personal/mypersonalsitename)

    But, when i access below url, i get this message "DataSource.Error: OData: The feed's metadata document appears to be invalid. Details: null"

    = SharePoint.Tables("https://xxx.sharepoint.com/sites/yyy") 

    When I access below URL in browser, surprisingly it works in browser

    https://xxx.sharepoint.com/sites/yyy/_vti_bin/ListData.svc

    Thursday, December 26, 2013 10:40 PM
  • When you look at that URL in the browser, does the XML document have an xml:base attribute -- and if so, what is it?

    Friday, January 03, 2014 4:38 PM
    Moderator
  • It does have xml:base attribute. Below are how it looks for both root site and sub site.

    Sub Site :

    <?xml version="1.0" encoding="UTF-8" standalone="true"?>

    "><service xmlns="http://www.w3.org/2007/app" xmlns:app="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="https://xxx.sharepoint.com/sites/yyy/_vti_bin/ListData.svc/">

         <workspace>some content</workspace>

    </service>

    Root site:

    <?xml version="1.0" encoding="UTF-8" standalone="true"?>

    "><service xmlns="http://www.w3.org/2007/app" xmlns:app="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="https://xxx.sharepoint.com/_vti_bin/listdata.svc/">

          <workspace> some content </workspace>

    </service>

    Friday, January 03, 2014 5:02 PM
  • And using the browser, can you access a metadata document at https://xxx.sharepoint.com/sites/yyy/_vti_bin/ListData.svc/$metadata

    ?

    Friday, January 03, 2014 6:56 PM
    Moderator
  • I'm getting the same result here. 

    <service xmlns:atom="http://www.w3.org/2005/Atom"
    xmlns:app="http://www.w3.org/2007/app"
    xmlns="http://www.w3.org/2007/app"
    xml:base="https://portal2010.mydomain.com/sites/parent_site/subsite/_vti_bin/listdata.svc/">

    When I try to access the metadata document, I get an error

    <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <code/>
    <message xml:lang="en-US">An error occurred while processing this request.</message>
    </error>

    However, interrogating the metadata document for the parent site works as expected. What would be causing the error?

    Friday, April 11, 2014 3:34 PM
  • Miguel,

    Has there been any fix on this? It seems like Power Pivot can import data from sharepoint subsites, but Power Query can't? Has anyone found a workaround for this? I am running into the exact same issue as SeanDB25.

    Thursday, October 09, 2014 5:42 PM
  • FYI - I was able to get around sub-site navigation with the SP List connection by using "From Web" in PQ and putting the direct url for the SP List.

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

    • Proposed as answer by Torben E Friday, October 10, 2014 2:06 PM
    Thursday, October 09, 2014 11:19 PM
  • Matt that looks somewhat viable. Kind of diminishes the value of a specific SP List source in PQ though. I like it as a workaround however.
    Friday, October 10, 2014 2:06 PM
  • Hi Matt,

    when I tested this method, it seemed to only retrieve the first thirty items in the list.  I am creating a view that is not paged, but I suspect the load times will end up timing out the query as the number of entries in the list grows.


    My specific isssue is that a power query file in a subsite cannot access a list in the root site.  Has there been any progress on this issue?
    Thursday, November 19, 2015 11:15 PM
  • I have the same issue since our company switched to SharePoint 2013. I found the following:

    If you add a SP list by URL as a PowerQuery source, Excel 2013 and Power Query 2.26.4128.242 adds the following source information to the query "= SharePoint.Tables("http://site.com/teams/subsite", [ApiVersion = "Auto"])".

    On SharePoint however the following file is missing: http://site.com/teams/subsite/_api/web/$metadata and this casues a 404 in my case.

    By simply removing the ApiVersion parameter, it displays all available lists within this particular subsite. Bottom line from my point of view, either SharePoint is missing something in the _api/web/ folder, or PowerQuery is missbehaving. 

    I did some tests with SharePoint 2010 and it works fine using the same PowerQuery version though!

    Friday, November 27, 2015 8:03 AM
  • I just found out that this issue occurs with power query version 2.26.4128.242 only. By using version 2.24.4064.242 for instance it works fine. 
    Wednesday, December 02, 2015 2:58 PM
  • Hello,

    I just had the very same issue. Check if any of your lists names have special characters or begin with a number. For me the problem went away when I renamed a list from "2015MyList" to "MyList".

    I've written up the details in this blog post.


    cheers, teylyn

    Wednesday, December 09, 2015 2:36 AM
  • PowerQuery is the issue here, definitely not the List name.
    Thursday, December 10, 2015 11:53 AM
  • In your case maybe, but in my case the problem definitely was with SharePoint. I'm still using SP2010. SharePoint 365 does not show the problem, either. 

    The file at http://site.com/teams/subsite/_api/web/$metadata  was not missing but threw an error. Renaming the list made that error go away.

    Just thought I'd share to save somebody some frustration. 


    cheers, teylyn

    Friday, December 11, 2015 12:51 AM
  • Hi All!

    Thanks, Matt, for your workaround! One has to make sure to increase the Item Limit of the list view - as Anikke mentioned, as Excel will not warn you if the number of items on a list exceeds the number shown on the page. (mine is under 5000...)

    Happy trying! :)

    Friday, March 18, 2016 2:22 PM
  • The list view threshold of 5000 items does not apply to Power Query. I use Power Query to pull hundreds of thousands of records from SharePoint into the Excel Data model. 

    cheers, teylyn

    Tuesday, March 22, 2016 1:05 AM