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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Power BI Service Failing to refresh SharePoint lists - Fine in Desktop -

https://community.fabric.microsoft.com/t5/Service/Power-BI-Service-Dataset-Refresh-Error-Expression-...

 

There is already an ongoing thread in the Community PBI Service forum but I thought it could do with being here too for visibility.

 

Symptoms:  SharePoint Online lists meeting certain criteria fail to refresh with error Expression.Error: The column '<ccon>ID' of the table wasn't found.. ID. </ccon>. The exception was raised by the IDbCommand interface. Table: [Table Name]

 

Refresh in Power BI service was working fine for these reports across multiple tenants until reports running from 9/8/2023 midnight UTC onwards.

 

Some possible trigger conditions noted in above thread:

 

V2 connector and empty SharePoint list created before a certain date on SharePoint Teamsite (classic w/o Team) 

V2 connector and ID column for the list isn't being imported to the data model (i.e. query filters it out)

 

I found in the second circumstance as long as the list has data in it you can get it to refresh by including the ID column in Power Query instead of removing it, but empty lists just fail completely.

 

@sulleyinoz mentioned there is a ticket open with MS about their issues with blank lists, is there any acknowledgement this is a wider issue and is being worked on?

 

Thanks

 

Mark

 

Status: Accepted

Update:

 

The issue is already fixed in all regions and clusters. A fix on this issue will be available around the mid-September release.

As a workaround for Power BI Dekstop for now, Customers can add the ID column back to the select statement within the M Query if customers are using ID column in "Table.SelectColumns" - Customers can remove the ID column within the M Query if customers are using ID column in "Table.RemoveColumns" 

 

Best regards.
Community Support Team_Caitlyn

 

Comments
Stephen_PDS
Regular Visitor

Can this be tackled ASAP for those of us who the work around, does not work for? Second time whatever change implemented has broken PowerBI. Least the first time we got a roll back when you couldn't repair the issue. 

lapol
Regular Visitor

Now I am getting the CCON error for other datasets that weren't originally an issue:

 

Data source error: [ValidateMarkupTags][ccon]Expression.Error: There weren't enough elements in the enumeration to complete the operation.. [ccon]#table({"Content"}, {})[/ccon]. [/ccon];[ccon]There weren't enough elements in the enumeration to complete the operation.[/ccon]. The exception was raised by the IDbCommand interface. Table: MasterScheduleLotSummary.
Cluster URI: WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID: 2abe984c-77e5-4290-9fb3-5be645aa81f8
Request ID: 9bdc397c-25cd-47f1-a4a4-f631539c277e
Time: 2023-09-23 07:23:25Z

 

Can we please get an update on this issue? 

Taryn
Frequent Visitor

Is there any plan for Microsoft to go back to not failing if the SharePoint list has no rows? I often include a list with no entries as part of my dataset, knowing that the list will be populated in the future. I don't want to have to remove them from the refresh (so it won't fail) and then monitor the list to see when an entry is added so that I can go back and edit the query.

Stephen_PDS
Regular Visitor

Still hoping for an update to this, whatever fix came in, in 'mid September' if there was one at all clearly hasn't resolved the issue