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.

Trevor Seward is a Microsoft Office Apps and Services MVP who specializes in SharePoint Server administration, hybrid scenarios, and SharePoint Online. He has been working with SharePoint for 16 years from SharePoint 2003 on up, managing environments with terabytes of content for 150,000+ user organizations. Trevor is an author of Deploying SharePoint 2016 and Deploying SharePoint 2019. You can find him on Twitter and in /r/sharepoint.