Unable to refresh dataset with Column content exceeding 315 characters

Hi pallavi_r ,

Thank you for your in depth research and for sharing your findings with the community.  Your persistence in testing multiple approaches OData Feed, SharePoint Folder, List 2.0, and REST API, provides valuable insights, and We  truly appreciate your effort.

1. The SharePoint REST API has certain design limitations and doesn't always expose all metadata fields by default. Attributes like OData_ComplianceTag and OData_ComplianceTagWrittenTime may not be retrievable unless they're explicitly exposed by SharePoint.

• If Power Query returns null values even after specifying these attributes, it means they aren’t accessible through the API. Unfortunately, Power Query can't override these limitations if the data isn’t available in the API response.

2. Since List 1.0 is slow and List 2.0 doesn’t contain all the required metadata, consider the following alternatives.

• Microsoft Graph API - This may offer more extensive metadata retrieval options.
• Power Automate - Use this to extract metadata and store it in a structured format for Power BI to consume.
• SharePoint PnP PowerShell Scripts - Leverage these to retrieve and store the necessary attributes in a more accessible format.

If none of the above methods work. To raise a support ticket, kindly follow the steps outlined in the following guide:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Your efforts in testing these methods are greatly valued. The community benefits immensely from your findings. If you need further assistance with any of the suggested solutions, don’t hesitate to reach out.

Regards,

Yugandhar.

Hi All,

This is my final findings on metadata capture from all sharepoint connectors.

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

4.Sharepoint Odata Feed - 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.

Slow because of custom column creation

Final conclusion is metadata at any level we can retrieve using these 3 methods above, the refresh challenge remains the same.

Thanks,

Pallavi