Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LCrossling
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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

View solution in original post

13 REPLIES 13
v-cgao-msft
Community Support
Community Support

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

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.

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
Regular Visitor

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
New Member

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
Advocate I
Advocate I

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
Regular Visitor

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. 

Fiasco_0-1691721510019.png

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
Frequent Visitor

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
Super User
Super User

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. 

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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.

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. 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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

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. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors