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

[mpwilliamson]

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

[alamosilva]

I have the same problem. 

Did someone solve this issue?

[Halkurn]

I was able to resolve the issue in one of my dashboards by unhiding the ID column in all my queries using the query editor.  After that it started refreshing on schedule again.  I'm waiting on changing the others to see if MS will solve the problem.

[paypal_matt]

Was banging my head on this one all day. My scheduled refresh started failing on Friday August 11th despite no changes to my SharePoint site structure, SharePoint List structure, or data in the SharePoint List. I was able to get the refresh going again today by following the advise above (changing my "Remove Other Columns" step to include the "ID" column explicitly, I was removing it implicitly before). Thanks for the helpful advise.

That said, as part of my debugging today, I switched from using the "SharePoint Online List" connector in my report to using the "Web" connector and the Get List Items endpoint on the SharePoint API. Interestingly enough, that worked fine (even when hidding the ID column). This issue is exclusive to the SharePoint Online List connector right now.

[Anonymous]

Hi  @all,

We have reported this issue and submitted it to the product team.
They have been aware of the issue and the engineers will do their best to resolve it. I will update here if there is any progress, so please be patient. 

Best regards.
Community Support Team_ Caitlyn

[Gerardo_marin]

I have the same problem

[Anonymous]

Hi @all,

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. 

Best regards.
Community Support Team_Caitlyn

[sailorman]

Unhiding the ID column in M query worked on V2.

[Anonymous]

What started to go wrong on the ID column is now occuring on the "Created" column, great. Unhiding does not work for me this time.

[Taryn]

Is there an ETA on the fix? It's been a week now.

[alamosilva]

It seems that was solved. My refresh scheduler is working fine.

Thanks!