Data refresh fails on online service but works fine on desktop app

[LCrossling]

Hi,

I've got numerous reports all connecting to the same sharepoint online data source. They have all been working fine untill recently. Now they have all failed with the error:
Expression.Error: The column '<ccon>ID' of the table wasn't found.. ID. </ccon>. The exception was raised by the IDbCommand interface. Table: Opportunity Form.

However the table causing the error is inconsistant among these reports.
When I refresh these in the desptop I get no erros and then I reupload them and they work as they did a few days ago. But the next day I checked them all again and they have the same issue again.

Any idea what could be causing this?

Many Thanks!

[v-cgao-msft · Accepted solution]

Hi all,

This is a known issue. I'll update here with any news.

Power BI Service Failing to refresh SharePoint lis... - Microsoft Fabric Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-cgao-msft]

Hi all,

2023-10-12 Update:
The fix for PBI Desktop will come in the November version.
Fix for PBI Service is by December 2023.
----------------------------------------------------------------------------------------------------

Update:

Engineering team is working on testing the fix and deploying.

As a workaround for now：

You may try add the ID column back to the select statement within the M Query. 
----------------------------------------------------------------------------------------------------
Update:

A fix on this issue will be available around the mid-September release.

[cygnex]

I think adding the referenced "ID" column from the error message to the table in question should fix the issue.

It worked for me.

[lapol]

Also noticed this issue today. Seems to be happening to all of the datasets that are linked to SharePoint lists. My datasets that are linked to csvs or other sources are not having this issue. 

[tarjeieo]

Seeing the same issue as of today in the PBI on the web. The report is updating fine from PBI Desktop, but after I publish, I still get the issue from PBI at https://app.powerbi.com/. SharePoint Online connection. Not using any ID columns. The error I'm getting is "Expression.Error: The column '<ccon>ID' of the table wasn't found.. ID. </ccon>. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: Prosjektrom."

[cygnex]

I just got back from vacation today and I am seeing this issue pop up for me also.

I know I did not change anything since I have been out for the past week.

I can refresh manually in Power BI Desktop but it fails in the service.

There must have been some type of update that is causing this. 

[Fiasco]

tl;dr: I figured out a workaround for my issue, though it is very case-specific.

Same <CCON>ID error here! Started this week. I narrowed the issue to the "ID" column that the SharePoint Online list gives me when I ""Source = SharePoint.Tables("https://---.sharepoint.com/sites/------", [Implementation = "2.0", ViewMode = "Default"])"".
When I selected other columns and processed further, it said ID was missing. When I selected column "ID" with my other columns I was fine. If I tried to remove the "ID" column, the error returned.
My query makes use of "Table.UnpivotOtherColumns" and I only have this issue popping up with this query; the rest are fine. Do you have anything unique like this in your query that can help pin down the issue?

Hopefully this is the helpful bit, I finally figured out a workaround. But, it may be case/cause specific. I added "ID" to my #"Removed Other Columns" = Table.SelectColumns. 

But, when I unpivot, I get extra rows added to contain my unpivoted "ID" values. I removed the extra rows by filtering out "ID". I haven't tried pivoting back after this but maybe that is a solution for others with this issue?

Like I said, this is a workaround. It isn't fixing the issue. But I hope I'm helping to get to the root cause and save frustration along the way.

[eklamm]

For what its worth, I had the exact issue @Fiasco described pop up today. Almost the identical scenario, using the Sharepoint Tables connection with the 2.0 implementation with an Unpivot Columns action that did not involve the ID column in any way.

I worked around it the same way you did, add ID back to the list of columns in when it was not selected previously, the unpivot and filter out the ID values. Certainly not ideal. Possibly a connector update? This report has been running on the service for months with no issue prior to today.

[rubayatyasmin]

Hi, @LCrossling 

Found some solved threads with similar errors. See if that helps.

https://community.fabric.microsoft.com/t5/Power-Query/Expression-Error-The-column-Column1-of-the-tab...

https://community.fabric.microsoft.com/t5/Desktop/Expression-Error-quot-The-column-of-the-table-wasn...

Also, if you are using Gateway check that it's up-to-date. Misconfiguration in the gateway or network issues could lead to such errors. 

Double check the Opportunity Form table in SharePoint source data for the specific column name. 

Also, open PQ editor and check step by step to see which query step is causing the issue. 

As you are only refreshing the desktop file it is not showing any error, try opening the PQ editor and see if it's working fine. 

[LCrossling]

It's not using any Gateways and when I open PQ there's no issues even after I refresh and I cant seem to replicate the error that it produces online.

[rubayatyasmin]

Can it be a caching issue? Can you delete the dataset and report from the WS and try building a new one then publish it? See if that works. 

[LCrossling]

I've refreshed the report in PQ and then uploaded it on onto the WS to replace the current one and however when I then try to refresh it it gets the same error message

[cygnex]

I added the referenced column from the error message, "ID", from the SharePoint list and then re-published.

I am now able to auto-refresh in the service again.

This is is another workaround, not the solution.