Calculate the pdf-files in a sharepoint subfolder (created every week)

[Brand]

Dear BI heroes!

I do not know if it is possible, but I would like to get some specific data out of Sharepoint.

I would like to know how much PDF-files are in a specific subfolder, but this subfolder changes every week.
This is because every week there will be new set of automatiscally generated pdf-files in a subfolder named after the weeknumber. For example: Year 2023/Week 39/ <PDF_FILE(s)>.

Results I would like:
Every monday we will need the number of PDF-files in the subfolder of last week. So today (8-7-2023, week 32) we would like to see (quantity) how much PDF-files are uploaded to the folder 31 (last weeks folder).

I'am using a default sharepoint import source in Power BI, but cannot seem to find a "easy" way to get the data...

[v-yanjiang-msft · Accepted solution]

Hi @Brand ,

To get the number of PDF-files in the subfolder of last week, you can try to use the SharePoint REST API to query the folder and filter by the creation date of the folder. Please try the steps:

1. Use the following URL to get the list of subfolders in the parent folder: `https://<your_sharepoint_site>/_api/web/GetFolderByServerRelativeUrl('<parent_folder_url>')/folders`

2. Loop through the subfolders and find the one with the creation date of last week. You can use the `Created` property of the folder to filter by date.

3. Once you have the subfolder for last week, use the following URL to get the list of PDF-files in the subfolder: `https://<your_sharepoint_site>/_api/web/GetFolderByServerRelativeUrl('<subfolder_url>')/files?$filter=File_x0020_Type eq 'pdf'`

4. The number of PDF-files in the subfolder can be obtained from the `__metadata.count` property of the response.

Best Regards,
Community Support Team _ kalyj

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