List files in SharePoint folder and subfolders by using .Contents THEN .Files (or similar)

[t_horrocks]

Hi,

I need to connect to a SharePoint site and list all the files in a particular folder including its subfolders. I have folders for each year with subfolders for each month. For example I want to connect to the '2024' folder and list every file in '01 - Jan', '02 - Feb' etc.

I can do this using 'Source = SharePoint.Files' and then filtering on 'Folder Path' using 'Text.StartsWith.'

However I do not want to have to load every single file on my SharePoint site first. (This method also tells me I have errors in my data although all rows load perfectly, when I try to identify the errors nothing shows up. Not a big problem, just annoying...)

I can also use 'Source = SharePoint.Contents' to navigate to a specific folder and list the files. This is a much better method. However I have to navigate to the lowest level folder to get to the files. For example I need to navigate all the way to the '01 - Jan' folder to see the January files.

Is there a way I can combine the methods, i.e. use .Contents to navigate to the 2024 folder and then use .Files to list all files within 2024? Or some other method of achieving this?

I have tried writing a complex query to do this but it is beyond me.

I have included the code for each method listed above if it helps:

1) Using .Files and filtering

let
// Get the SharePoint contents from the specified site
Source = SharePoint.Files("https://my.sharepoint.com/sites/MySharePointSite", [ApiVersion = 15]),
//Filter on 2024 folder
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://my.sharepoint.com/sites/MySharePointSite/Automation/Files/2024"))
in
#"Filtered Rows"

2) Using .Contents and navigating to lowest folder

let
Source = SharePoint.Contents("https://my.sharepoint.com/sites/MySharePointSite", [ApiVersion = 15]),
Automation = Source{[Name="Automation"]}[Content],
#"Files" = Automation{[Name="Files"]}[Content],
#"2024" = #"Files"{[Name="2024"]}[Content],
#"01 - Jan" = #"2024"{[Name="01 - Jan"]}[Content]
in
#"01 - Jan"

Many thanks in advance. 

[lbendlin · Accepted solution]

You can do that easily with recursive functions and SharePoint.Contents.  However, when you are done with that you will notice that it is not any faster than using SharePoint.Files.  Your choice.

What do you do next with the data you collected?  Does this even need Power BI or could this have been done via the SahrePoint batch API?

[t_horrocks · Accepted solution]

Thanks Ibendlin.

If there is no performance improvement to be had then I guess i'll just stick with filtering the files.

I then combine the contents of the files, performance some transformations and produce a series of reports in Power BI.

[v-zhengdxu-msft]

Hi @t_horrocks 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

You can do that easily with recursive functions and SharePoint.Contents.  However, when you are done with that you will notice that it is not any faster than using SharePoint.Files.  Your choice.

What do you do next with the data you collected?  Does this even need Power BI or could this have been done via the SahrePoint batch API?