Excel Get Data from SharePoint - How to list all files, including subfiles within subfolders?

Question

Hello!

I'm trying to pull all the documents from our SharePoint to be listed in an Excel Spreadsheet with the document name, when the document was created, it's retention label (identifying it as a record and what kind), when it was set as a record and who set it. This is so I can take that data and put it into some nice pretty pie charts for our heads of department to show what percentage of the documents on our SharePoint are records and what kind of records are being declared.

I'm able to pull the data from each document library on the SharePoint that are listed in the Site Contents, however it only seems to pull the surface level documents and folders from each library and doesn't include all the documents and folders within those folders.

How can I drill down in the query to list all of the other files in the many (many) layers of file structure? Preferably so I can have it all in one query and sheet and don't have to spend the rest of my life creating queries for each file structure and then merging them!!

I'm using Microsoft Apps for Enterprise. Excel v2302 (Build 16130.20916)

Any help with this problem or a better way to do it would be greatly appreciated!!

Cheers,

Ryan

Answer 1

Data>Get data>From sharepoint folder.

Based on my test, it will get all the file include sub folders.

Answer 2

It is limitation in Power query. You may send feedback to Microsoft to combine the features.

Excel · Community (microsoft.com)

Send feedback to Microsoft with the Feedback Hub app - Microsoft Support

Or raise a support ticket to Microsoft.

Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. Support team there will have the correct channel and resources to help you send it to product team.

Get support - Microsoft 365 admin | Microsoft Learn

Answer 3

Ah, I thought 'From SharePoint Folder' would only pull a specific folder, so I was pulling From SharePoint Online List instead!!

Using From SharePoint Folder does pull all of the files on the SharePoint, however it doesn't pull all the metadata details like it does with the SharePoint Online List, so I can't see information such as Retention Labels. It only seems to pull the basic file information and has no ability to expand the columns in the query editor, other than to show the file type.

Below is a screenshot of all the available columns from the SharePoint Folder Query

And below is a screenshot of the columns that I've kept out of the many columns it pulls from the SharePoint Online List

Is it possible to be able to pull that same query data but with the information that the SharePoint Online List searches for?