Excel Power Query – Loading SharePoint Document Library Data

With Excel Power Query, you can query SharePoint List data quickly and easily, as well as a variety of other types of data sources.



However, when we do this, only SharePoint Lists appear!  This is not helpful if, for instance, you have property promotion from an InfoPath form into a Document Library that you want to query.



To work around this, we simply need to show the Formula Bar, which can be found in the Query Editor window under Settings.



Next, edit the query to show all Contents of the SharePoint site.  Once we change =SharePoint.Tables(“http://url”) to =SharePoint.Contents(“http://url”), click the Refresh button and all Document Libraries will appear!




The other valid value for the SharePoint query type is =SharePoint.Files(“http://url”).  With this type, all uploaded files on the SharePoint site will be displayed.



There is a 4th query type, SharePoint.Count, which is ignored in the Power Query assemblies.


  1. Great tip. unfortunately, even if the user can reach an InfoPath form library or a document library, the promoted metadata columns (where the valuable data resides) do not appears in Power Query :( Does anyone has a trick to reach out those custom document metadata columns???

  2. I am trying to establish a Connection from Excel 2010 to SharePoint 2010 Server and alternatively to a Foundation. Both Systems are accessed via https and with both Connections I am getting no list of data, just the first entry showing the query itself. As Login I have choosen Windows with my credentials which works fine for other Solutions.

    Do you know if there is a restriction to https-connected sites?

    Thanks for your Feedback in advance.


  3. Any update on whether it is possible to connect Power Query to an InfoPath library and show promoted columns?

  4. Do it as an oData feed and it works fine Steve. The trick I am trying to figure out now is if I can actually pull all the InfoPath form data that isn’t prompted into one reportable table as there are details I want to see.

Leave a Reply