Unable to refresh dataset with Column content exceeding 315 characters

[pallavi_r]

Hi All,

I am facing one issue while refreshing a dataset with column length more tan 315 characters. It does not throw proper error, Just refresh stops with invalid credentials. And if i decrease the characters, it gets refreshed successfully. 

The dataset is very less, just 4-5 mb.

Any idea how to resolve this one?

Can I change it to any specific data type which can accomodate more chars.

Thanks,

Pallavi

[V-yubandi-msft · Accepted solution]

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.

[V-yubandi-msft]

Hi @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.

[pallavi_r]

Hi @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 · Accepted solution]

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.

[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 · Accepted solution]

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

[pallavi_r]

Thank you @lbendlin  @Poojara_D12 @V-yubandi-msft  for quick reply. All your points seems right to me. Its not the column length, rather when the file length is more than 315 in combination with expanded column attributes in the power query steps, it is causing the issue. I have not been able to resolve it yet. May be is it because I am using sharepoint online list 1.0 which is causing refresh issue.  Still figuring out.

Thanks,

Pallavi

[V-yubandi-msft]

Hi @pallavi_r ,

Thank you for your update.  Based on your findings, it looks like the issue might be due to limitations in SharePoint Online List 1.0 and expanded column attributes in Power Query.

• SharePoint Online List 1.0 has known limitations, and upgrading to List 2.0 could provide better performance and stability.
• SharePoint Online has API limits, especially when retrieving large data with expanded columns,      which might be affecting the refresh.

Could you please try switching to SharePoint Online List 2.0 and simplifying Power Query steps to see if it resolves the issue? Let me know the results, and I'll be ready to assist you with any further steps.

Best regards,

Yugandhar.

[pallavi_r]

Hi @V-yubandi-msft ,

Yes, Online List 1.0 is slow, but if I upgrade to List 2.0, the required metadata is not found in List 2.0. It seems list 2.0 has limited metadata compared to List 1.0. Please correct me if I am wrong.

Is there a way I can use List 2.0 and call out these specific expanded column attribute in power query step which are actually not available in List 2.0.

Kindly suggest.

Thanks,

Pallavi

[V-yubandi-msft]

Hi @pallavi_r ,

Thank you for your quick response and for sharing your experience and knowledge. Using SharePoint's REST API to directly fetch the required metadata from SharePoint Online seems like a useful workaround. Have you already tried this method?

Regards,

Yugandhar.

[pallavi_r]

Hi @V-yubandi-msft ,

I am going to try this today. Could you please do me a favour sharing some reference link or sample code to extract specific metadata from SharePoint call via rest api.

Your suggestion will be a great help to me.

Thanks,

Pallavi

[V-yubandi-msft]

Hi @pallavi_r ,

We hope you had the chance to try the SharePoint metadata extraction suggestions we shared. Could you please let me know if they worked for you?

If my response resolved your query, please mark it as the Accepted Solution to assist others. Additionally, I would appreciate a 'Kudos' if you found my response helpful.

[lbendlin]

Can you specify what you mean by "specific"? Can you give an example?

[pallavi_r]

Hi @lbendlin ,

The specific field that I want to capture but not limited to

Odata_ComplianceTag,

Odata_ComplianceTag,

That's available in Items.file.listitemallfields.fieldvalueastext 

Along with this I capturing other metadata like childcount, empty folder, file url etc etc.

Thanks,

Pallavi

[V-yubandi-msft]

Hi @pallavi_r ,

Thank you for reaching out to the Microsoft Fabric Community with your query.  The solution provided by the @lbendlin & @Poojara_D12 is accurate. Here are some additional points to consider.

•  Ensure that the column type in the source system (SharePoint list) is set to allow for longer text entries. If   it's a single line of text, consider changing it to a multiline text field.
•  If the file names are consistently exceeding the character limit, consider using Power Query   transformations like Text.Middle() or Text.Start() to split or truncate the text safely before loading.

For more detailed guidance, you can also refer to the following resources, which might be helpful for you.

Link:  How to increase Character Limit for Multiline Text Field in SharePoint - Microsoft Community.

If my answer addressed your query, kindly mark it as the Accepted Solution to assist others.

I'd also be grateful for a 'Kudos' if you found my response useful.

[Poojara_D12]

Hi @pallavi_r 

As per the previous comments what I think is the issue with dataset refresh stopping without a clear error when a column exceeds 315 characters may be due to data type constraints, connector limitations, or backend storage restrictions. In Power BI, Text columns generally support long values, but some sources impose hidden limits. If using SQL Server, ensure the column is NVARCHAR(MAX) or TEXT instead of a fixed length. For Excel or APIs, consider truncating or splitting the text before loading. If using DirectQuery, long text fields might not be fully supported, so switching to Import Mode could help. Additionally, Power Query transformations like Text.Middle() or Text.Start() can limit the length safely. Debugging by manually reducing text length, checking Power BI logs, or isolating the column can help pinpoint the issue. As a workaround, storing long text separately and linking via a key can prevent failures.

 

[lbendlin]

what is "column length" ? Size of the column name, or size of the column content?

There are various limitations that sometimes even combine , for example "10000 rows or 1000000  elements, whichever comes first").

What's the source of your semantic model?

[pallavi_r]

Hi @lbendlin ,

It's the content of a column that exceeds 300+ chars and causes refresh issue 

Thanks,

Pallavi

[lbendlin]

Text in columns can be up to 32K characters long. There must be something else going on.  What's the source of your semantic model?

[pallavi_r]

Hi @lbendlin 

The source is SharePoint list to capture the metadata where I have further expanded the attributes in the power query.

Thanks,

Pallavi

[lbendlin]

Then the limitation is likely coming from the source. Single line of text or multi line?