Show all site content when importing from SharePoint in Power Query

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to this blog include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Show all site content when importing from SharePoint in Power Query
When you're importing data from a SharePoint site using Power Query in Excel, sometimes you don't see a full list of the files stored in the site. This blog explains how to edit a query to reveal the full contents of the SharePoint site.

Posted by Andrew Gould on 03 March 2026

In this blog

Missing files when importing from a SharePoint site
Changing the function used to list SharePoint site contents
Navigating to a folder
Extracting the file contents

Missing files when importing from a SharePoint site

In Excel, you can choose to import data from files stored in a SharePoint site. For this blog, I'd like to import data from an Excel file called Music_01.xlsx in a folder called Excel Multi Table Models.

Excel workbooks stored in a SharePoint folder

Here's the file I want to import, along with some other Excel files in the same folder.

The problem is that the file doesn't appear when I use Power Query to access the SharePoint site. In the image below, I've added a filter to show all the files in the Excel Multi Table Models folder.

A filtered list of Excel files stored in a SharePoint folder in Power Query

The other files are there, but the one I want isn't!

So, how do we get to the file we want?

Changing the function used to list SharePoint site contents

When you create a query to import data from a file stored in a SharePoint site (read this blog if you're not sure how to do that), Power Query uses the SharePoint.Files function to return a list of files.

You can see this by selecting the Source step of the query and looking in the formula bar (choose View | Formula Bar from the Power Query ribbon if you can't see it).

A basic Power Query query to return a list of files stored in a SharePoint site

Select the Source step to reveal the code in the formula bar.

The SharePoint.Files function should return a list of all the files in the site, but sometimes it doesn't!

You can edit the code of the Source step to change how the site contents are returned. Try changing SharePoint.Files to SharePoint.Contents instead.

Using the SharePoint.Contents function in Power Query to list the contents of a SharePoint site.

The query now lists the contents of the site, rather than the files stored in it.

The SharePoint.Contents function lists all of the folders and files stored at the top level of the SharePoint site, rather than just the files stored at any level of the site.

Navigating to a folder

Next, we need to navigate to the folder containing the file we want to import. Start by clicking the Table link next to the parent folder to extract its contents.

Extracting the contents of a SharePoint folder in a Power Query query in Excel

The parent folder in this case is Shared Documents. Click the Table link to extract its contents.

The query extracts the contents of the folder and presents it in a new table. Continue clicking the Table links to navigate through the folder structure.

Navigating to a SharePoint site folder in a Power Query query

The next folder in our example is Excel Multi Table Models.

Once you've reached the final folder, your missing file should be revealed!

A list of files in a SharePoint folder displayed in Power Query

There's the file we want!

Extracting the file contents

To extract the contents of a single file, click the Binary link in the Content column next to the file name.

Extracting the contents of an Excel file in Power Query

Click Binary next to the file name to get its contents.

Next, you can choose which item in the file you want to import. To do this, click the Table link in the Data column next to the item you want to import.