How to capture document metadata of a sharepoint list from subfolder item

[pallavi_r]

Hi All,

I am trying to capture the sharepoint online list items. The sub folder child items are not getting captured. Is it not feasible to capture list items specifically 'Retention Label' of child folder items via sharepoint online list v2.0. Kindly suggest.

Thanks,

Pallavi

[pallavi_r · Accepted solution]

Hi All,

What I found the sharepoint folder & odata returns the sub folder child items detail but does not capture the retention label. And Sharepoint online list v2.0 captures the retention label but does not show the subfolder items.

Finally I tried sharepoint online list 1.0 which displays all attributes, child item attributes, expanding each of those columns, finally I found retention label of subfolder items. The column for retention label - Odata_Compliance_Tag.

I see lot of posts here says that 1.0 is slower to refresh and not recommended. But as of now this option is solving the purpose it seems. Will keep here posted on other option using Rest API call.

Thanks,

Pallavi

[pallavi_r · Accepted solution]

Hi All,

I would like to put down my findings done so far w.r.t sharepoint metadata and close this as final thread, so it will help anyone who is looking for all possibility and feasibility.

1. Sharepoint List 2.0 - shows parent level metadata only

Fast, but does not meet the requirement

2. Sharepoint List 1.0 - shows all the metadata for all sub folder items, folder items

Slow because attribute expansion is at many level down

3. Sharepoint Rest service - Had to create a custom column for the same passing list item id and expanding the field value as text. Was able to retrieve all the metadata.

/_api/web/lists/getbytitle('documents')/items(id)/FieldValuesAsText

Slow because of custom column creation

****update as of 18-02-2025

4. Sharepoint Odata Feed - Was able to retrieve all the metadata, comparatively faster than all other methods. struggle is going on with make it ready for schedule refresh as it shows dynamic data source and cannot be refreshed. Working on that.

Final conclusion is metadata at any level we can retrieve using these 3 methods, now the challenge is to handle the slow refresh.

Thanks,

Pallavi

[pallavi_r · Accepted solution]

Hi All,

What I found the sharepoint folder & odata returns the sub folder child items detail but does not capture the retention label. And Sharepoint online list v2.0 captures the retention label but does not show the subfolder items.

Finally I tried sharepoint online list 1.0 which displays all attributes, child item attributes, expanding each of those columns, finally I found retention label of subfolder items. The column for retention label - Odata_Compliance_Tag.

I see lot of posts here says that 1.0 is slower to refresh and not recommended. But as of now this option is solving the purpose it seems. Will keep here posted on other option using Rest API call.

Thanks,

Pallavi

[v-kpoloju-msft]

Hi @pallavi_r,

Thank you for reaching out to the Microsoft Fabric Community Forum.

We really apologies for the inconvenience, after reviewing the issue of how to capture document metadata of a SharePoint list from subfolder item, here are few steps to may resolve the issue.

Please go through the following steps to resolve the issue:

• In Power BI Desktop, navigate to home >get data >more >online services SharePoint Folder. Enter your SharePoint site url. Power BI will access and retrieve all files and folders from the designated SharePoint folder. Once the data is loaded, select Transform Data to open the Power Query Editor.
• In the Power Query Editor, find the Folder Path column, which contains the complete path to each file or folder. Utilize this column to filter for specific subfolders by applying text filters, such as Text.Contains ‘Folder Path’, subfolder name, to capture files only from the desired subfolders.
• To access the actual file content, expand the content column. Additionally, expand other essential metadata columns such as Name, Date Modified, Folder Path, Author, and File Size as needed.
• If your SharePoint library includes custom columns such as Category or Status, you can integrate these with your document data. Utilize the SharePoint List connector to retrieve additional metadata from associated SharePoint lists and merge them using common fields like File Name or ID.
• Remove any unnecessary columns, such as system generated ones. Rename columns for clarity, for instance, change name to filename. Additionally, filter out duplicate or unwanted items based on your criteria, such as Date Modified or File Type.
• After cleaning and transforming the data, select close & apply to load it into Power BI for analysis and reporting.

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

Thank you.

[pallavi_r]

Hi @v-kpoloju-msft ,

Thanks for reply. Will try these steps and get back. 

Just a quick question, I am able to get child folder contents via OData connector and sharepoint file/folder, but the metadata column for retention label is not there. Only while connecting through sharepoint list, I get the metadata column - retention label captured at root label.

I am new to sharepoint library, not sure what I am missing here. Kindly advise.

Thanks,

Pallavi

[Akash_Varuna]

Hi Pallavi ,

try these if you havent

• Enable recursive querying in Power Query to capture all child items from subfolders.
• Use the Folder connector instead of the List connector if items are in document libraries.
• Expand hierarchy-related columns (e.g., Parent Folder Path) to fetch hidden subfolder items.
• Ensure you have permissions to view and access subfolder content in SharePoint.

if this post helped accept this asthe solution and give a kudos

[pallavi_r]

Hi @Akash_Varuna , Thanks for replying. Have tried all these but I am unable to get the Retention label in folder/file/odata connector for subfolder items. Only with sharepoint list, it shows retention label for root folder items.

Please let me know how can I capture retention label for sub folder items.

Thanks,

Pallavi

[Akash_Varuna]

Hi Pallavi , Strange that did'nt work but now i think you could use the sharepoint API for connecting to PowerBI and provide the details of subfolder you need in that API endpoint and in PowerBI GetData through web 
Secondly you could try using dataflow in PowerBi for your particular use case i guess

[Akash_Varuna]

Hi Pallavi , Strange that did'nt work but now i think you could use the sharepoint API for connecting to PowerBI and provide the details of subfolder you need in that API endpoint and in PowerBI GetData through web 
Secondly you could try using dataflow in PowerBi for your particular use case i guess