Issue with OData Connection to SharePoint -- "Errors in Web Data"

[renewtx]

Hello,

I recently created a report that runs a query for documents against SharePoint and this worked fine originally .. or at least so it seemed.  I did not notice any issue early on.  Once I had it working, I published it and all seemed well.  I then proceeded to make some modifications to the report filters in the PowerBI Desktop and in the process I forced a data refresh.  When I did that I started getting the below error -- can anyone shed any light on this error? I cant find anything online and I have not idea what it is attempting to do there ... it looks like it is a query that was automatically added to the report by PoweBI because I do not recall adding it explicitly ...

=================================================================

DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (The method or operation is not implemented.)
Details:
DataSourceKind=OData
DataSourcePath=https://sometenant.sharepoint.com/_api/Web/CardDesigns
SPRequestGuid=52f47ca1-10a6-7000-c645-6cdaaaa1a8e8
Url=https://sometenant.sharepoint.com/_api/Web/CardDesigns

=================================================================

Any insight would be appreciated ...

Thanks!

[Ritaf1983]

Hi @renewtx 

The error you're encountering seems to be related to a specific step in your Power Query process. Based on the screenshot and details provided, the issue occurs during the "Reordered Columns" step.

Here are steps to troubleshoot and resolve the error:

1. Review the "Reordered Columns" step. It is possible that the columns you're trying to reorder have changed in the source data. Open the query in Power Query Editor, navigate to the "Reordered Columns" step, and verify that all referenced columns are present and correctly named.

2. Check for structural changes in SharePoint. If columns in the SharePoint list or library were renamed, removed, or added, this could cause the error. Ensure the data structure matches what Power BI expects.

3. Remove or adjust auto-generated steps. Power BI sometimes creates unnecessary steps during query creation that may not align with the current data structure. Review all applied steps in the Power Query Editor and remove any that are not needed.

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

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

[renewtx]

Hi,

Sorry I am just now getting back on this ... I am not sure I follow ... according to the Power BI the error occurs in the Web data query, which does not include a 'Reordered Columns' step (screenshot):

In addition it refers to something called CardDesigns (any idea what that is?)

As far as "structural changes" in SharPoint, I'm not querying a specific list but running a query against a specific content type.  The same content type (with same structure) exists in both of my tenants.  The only difference is the Content Type ID which I changed in my query criteria when I switched tenants ... and I know that works because I originally got results against both tenants.

I checked my other queries and the ONLY one that has a step called 'Reordered Columns' is the query for Tenant Sites, but that is not the one that the error refers to ... it refers to Web Data

And even after I removed that step from the Tenant SItes query, I still get the same error ...

Thanks

[Anonymous]

Hi, @renewtx 

You need to verify the OData endpoint, if a 404 or 500 error is returned, the endpoint does not exist or is misconfigured. If data is returned, check that the response content is as expected (e.g., JSON structure).

In the Power Query editor, find the query named Web Data and check its resulting URL (it may have hidden extra parameters). Remove or remediate CardDesigns-related steps (which may be auto-generated redundant queries).

In SharePoint for the new tenant, check that the target content type exists and that the ID is exactly as in the query (including case and symbols). Use a standard REST API path (e.g. /_api/web/lists/GetByTitle('ListName')/Items) instead of directly referencing the content type ID. You can learn from the following link:

Get to know the SharePoint REST service | Microsoft Learn

Make sure that the account you use for Power BI has Read permissions for SharePoint lists and View permissions for content types in the new tenant. Temporarily grant the account full control permissions in SharePoint to see if the error persists.

If the problem is caused by a query automatically generated by Power BI, create a new blank report and manually configure the OData connection again to avoid relying on the automatically generated steps. Explicitly specify the URL of the list or library (e.g. /_api/Web/Lists/GetByTitle('Documents')/Items)

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[lbendlin]

Also note that Power Query column order will be ignored in Power BI, so is likely futile.