Forum Discussion
Super UserUnable 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 LearnYour 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
Community SupportHi pallavi_r ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you or let us know if you need any further assistance?
Your feedback is important to us, Looking forward to your response.
Thank You.
Super UserHi V-yubandi-msft ,
The issue is not yet resolved.
I have tried pulling the required metadata using Rest API, though the refresh was super fast than all other sharepoint connector, but it returned only limited attributes.
Exclusively if i edit the power query and add these metadata name for extraction, it gives null whereas in actual sharepoint site, it has values.
These are the retention metadata that I am looking for - OData_ComplianceTag & Odata_ComplianceTagWrittenTime.
Below is my observation after doing all these 4 exercises expanding the column attributes as much as possible to get the required metadata
1. Odata Feed - Limited Attibutes, does not return retention label metadata
2. Sharepoint folder - Only file content and file related attributes, no retention related metadata captured
3. V2.0 - Captures retention label, but all metadata are at library level (parent directory), does not capture the sub folder level metadata
4. Rest API - Very powerful in terms of capturing millions of metadata within minutes. But has limited attributes. Does not capture the retention label.
V-yubandi-msft - 1. ) Can you please confirm if this rest api is limited with this design and exposes only limited attributes. We can modify the power query editor to extract these compliancetag metadata?
2) All I see is 1.0 is the only option, but it is extremely slow and creates refresh issue. Can you please suggest if there is another way to capture the retention label attributes other than 1.0.
Kindly suggest.
Thanks,
Pallavi
- V-yubandi-msft
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 LearnYour 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.
- pallavi_r
Hi V-yubandi-msft ,
Thank you for your response.
I am able to retrieve the compliance tag information using Power BI Rest service using below code. Still working on it to get the details for all items.
/_api/web/lists/getbytitle('documents')/items(id)/FieldValuesAsText
Will keep here posted once I get success and share my findings.
Thanks for sharing other options. I shall check those as well.
Thanks,
Pallavi
- pallavi_r
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