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.

PowerQueryListData

 

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.

PowerQueryLists

 

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

PowerQueryShowBar

 

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!

PowerQueryContents

 

 

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.

PowerQueryFiles

 

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

6 Comments

  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.

    Frank

  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