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

[Darius7]

Hi all, I am new to Power BI. I am not sure how to do the workarounds listed above. Can someone help so that I can refresh my list? The error message I get is: 

Data source error: {"error":{"code":"DMTS_OAuthTokenRefreshFailedError","pbi.error":{"code":"DMTS_OAuthTokenRefreshFailedError","parameters":{"ConnectionDetails":"{\"kind\":\"SharePoint\",\"path\":\"https://directlinegroup.sharepoint.com/sites/ControlHub\"}","CredentialType":"OAuth2","DatasourceType":"SharePointList"},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"AADSTS53003: Access has been blocked by Conditional Access policies. The access policy does not allow token issuance.\r\nTrace ID: 3b8ac693-a32f-43b3-83d6-62ac947c4d00\r\nCorrelation ID: fb06b26e-3a18-45bb-9518-bc32a5454a2d\r\nTimestamp: 2023-08-30 08:57:18Z"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"AADSTS53003: Access has been blocked by Conditional Access policies. The access policy does not allow token issuance.\r\nTrace ID: 3b8ac693-a32f-43b3-83d6-62ac947c4d00\r\nCorrelation ID: fb06b26e-3a18-45bb-9518-bc32a5454a2d\r\nTimestamp: 2023-08-30 08:57:18Z"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2146233088"}}],"exceptionCulprit":1}}} Table: table.
Cluster URI: WABI-NORTH-EUROPE-I-PRIMARY-redirect.analysis.windows.net
Activity ID: 6dfa962b-c2fc-4de7-882f-cadcc429c870
Request ID: 1efaa9c3-1802-119e-230b-79a9b0825be7
Time: 2023-08-30 08:57:19Z

[mpwilliamson]

@v-xaoyan-msft - unfortunately this issue appears to be partly back for refreshes started since Midnight UTC on UK South.  The reports affected have not been changed or republished and were working fine since the fix was put in on 20th August 2023.  I've found the error seems to happen using the V2 connector for empty lists where the ID column is removed using Table.SelectColumns.

Data source error: The 'dd_DeliverableId' column does not exist in the rowset. Table: dd_Projects.
Cluster URI: WABI-UK-SOUTH-redirect.analysis.windows.net
Activity ID: 8cc307bd-c642-4a99-84e5-c976b286ffb4
Request ID: 2ef0024d-c5f8-3877-c04a-8c2ac6ca4ddf
Time: 2023-09-04 10:34:05Z

I think there's probably a dependency on the age of the list being connected to as lists I've just created aren't showing the issue but older empty lists are (the one causing the error here was created in April 2021).

Thanks

Mark

[v-xiaoyan-msft]

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

[Taryn]

@v-xiaoyan-msft 

It was fixed, but it looks like you broke it again.

[Stephen_PDS]

Does appear to still be an issue, any better idea of when the Mid Sept fix will come into force?

[Taryn]

Now I'm also seeing this error on an empty table that has a date field.
Data source error We're sorry, an error occurred during evaluation.;Index was outside the bounds of the array. Index was outside the bounds of the array. Index was outside the bounds of the array. Index was outside the bounds of the array. Index was outside the bounds of the array.. The exception was raised by the IDbCommand interface. Table: End Date.

[gramla12]

Still having this issue...

[Stephen_PDS]

Can we be provided with an update on this issue and when the Mid Sept release actually is?

[wernerengelen]

The issue was fixed in August and now is back? When is going to be finally fixed?

[mpwilliamson]

This issue still isn't fixed and it feels like we're beyond "mid-September" with this month's Power BI Desktop release also out of the way.  There's no sign of it in "Awareness" or "Known issues", do Microsoft still have it open as an actual issue, because it's still broken?