Power Query cannot pull lists from SharePoint subsites


  • 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


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.



    Friday, August 23, 2013 3:27 PM
  • 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.


    Wednesday, September 04, 2013 6:39 PM
  • 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("")

    = SharePoint.Tables("

    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("") 

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

    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
  • 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="" xmlns:app="" xmlns:atom="" xml:base="">

         <workspace>some content</workspace>


    Root site:

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

    "><service xmlns="" xmlns:app="" xmlns:atom="" xml:base="">

          <workspace> some content </workspace>


    Friday, January 03, 2014 5:02 PM
  • And using the browser, can you access a metadata document at$metadata


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

    <service xmlns:atom=""

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

    <error xmlns="">
    <message xml:lang="en-US">An error occurred while processing this request.</message>

    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.

    • 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("", [ApiVersion = "Auto"])".

    On SharePoint however the following file is missing:$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$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
  • Hi Miguel,

    I'm experiencing this issue on a SharePoint site (instead of a list). 

    Specifically, I'm able to pull from the site using..

    • Source=Web.Page(Web.Contents(""))
    • Source = Excel.Workbook(Web.Contents(""), null, true)

    But I receive same error mentioned in this thread when I attempt to use the following...

    • SharePoint.Contents("")
      DataSource.NotFound: SharePoint: Request failed (404): The remote server returned an error: (404) Not Found. (Not Found)
    • SharePoint.Files("")
      DataSource.NotFound: SharePoint: Request failed (404): The remote server returned an error: (404) Not Found. (Not Found)
    • SharePoint.Tables("")
      DataSource.NotFound: SharePoint: Request failed (404): The remote server returned an error: (404) Not Found. (Not Found)

    Is there a solution?


    Thursday, June 16, 2016 8:04 PM
  • Are you sure that the File.xls sits at the root of the SharePoint site? That path looks wrong on so many levels. There should be at least a SharePoint library in the path between site name and file name.

    cheers, teylyn

    Thursday, June 16, 2016 9:07 PM
  • If you want to open a file stored in SharePoint, you need to have the true URL for the file. The easiest way to get that is to open the file from SharePoint in Excel, then click on the File menu and then on the folder name (which is just under the file name and looks like "Server >> Path1 >> Path2 >> Path3 >> Folder". When you click on this, you should get a small menu with the choice "copy link to clipboard". Paste that into the Power Query "From Web" dialog. It will look something like

    Then, remove the "?web=1" so that the URL ends with .xlsx.

    Friday, June 17, 2016 1:50 PM
  • This can be done.

    Do not link directly to the list. Instead, in the list url field put only the url to the subsite root.


    This will show you a list of the Lists.

    Then you can navigate to the list you want from the list of Lists on that subsite

    Here is some code to that you can edit to get data from subsite List:

        Source = SharePoint.Tables("", [ApiVersion = 14]),
        MyListName = Source{[Name="MyListName"]}[Content]

    • Edited by sitrucp Tuesday, July 18, 2017 1:37 PM
    Monday, July 17, 2017 9:00 PM