Power BI Service Dataset Refresh Error - Expression.Error: The column '<ccon>ID' of the ...

[sulleyinoz]

---------  UPDATE 9th Oct 2023 - Refreshes failing again with empty tables from 9th Sept udpate --------------

So, it seems it started to fail again as of the 9th September.

Same issue exactly as the original failure ... empty tables won't refresh and cause the entire refresh on PowerBI Service to fail.

Desktop works fine as usual.

Seriously Microsoft, I have lost a client over this refresh issue.

--------- UPDATE 21th AUG 2023 at 9am AEST -------------

I believe this issue has now been resolved - certainly for me.  My refreshes are back to working as tehy were originally without any changes on my side.

Thanks @microsoft

Cheers

Andre

--------- UPDATE 18th AUG 2023 @4pm AEST ---------------

Microsoft have advised me they are working on a fix and are looking to deploy on 20th August. 

My concern is it doesn't talk about empty tables in teh fix, so they need to ensure it also covers tables in the case of no rows (and hence no ID ) being present. @v-rongtiep-msft 

Cheers

Andre

--------- UPDATE 15th AUG 2023 @8pm AEST ---------------

Hi Everyone,

Starting to see this issue propogate everywhere - particularly in ASIAPAC.  Please raise a support ticket with Microsoft asap and refer them to the original ticket I rasied - see details below.  This is going to be our fastest way to get a bulk result fast.  They will most likely want to understand the problem in depth and do diagnostics to confirm the same issue.

TITLE:  Power BI Service Refresh Error: The column &#39...

TRACKINGID#:  ************0278

RAISED: Monday 14th Aug, issue appeared SAT 12th Aug

SEVERITY A reduced to B (due to my ability to commit to answer calls 24/7 to support them to support me)

MY ISSUE SUMMARY: 

• Pre-existing legacy sharepoint lists in classic team sites that are EMPTY cause the Power BI Service refresh to fail using Implementation 2.0.
• It seems the same issue is occuring with power queries where the ID column has been removed and the list has data - I never had this issue because all my tables have the ID column already included. 
• Desktop refreshes work fine.
• We have also proven that newly created empty lists work fine if they have the ID column included
• If you have another variant on this, please update them on your nuances.

Fingers crossed we get this sorted asap and your support is much appreciated.

Cheers

Andre

--------- ORIGINAL MESSAGE BELOW -------------

So what makes this curious is that without warning and with no data changes or any other changes, refreshes that have been working for more than 2 months -  4 times a day  - suddenly started failing overnight Saturday night (AEST - UTC+10HRS).

I'm wondering if something has changed on the PowerBI Service?

Last refresh failed: 13/08/2023, 15:51:25
There was an error when processing the data in the dataset.Hide details

Data source error:	Expression.Error: The column '<ccon>ID' of the table wasn't found.. ID. </ccon>. The exception was raised by the IDbCommand interface. Table: Tools.
Cluster URI:	WABI-AUSTRALIA-EAST-B-PRIMARY-redirect.analysis.windows.net
Activity ID:	7f045518-3bd5-4ec6-8502-6b4a8e3385f5
Request ID:	2340146d-f897-4e91-bda3-f32f8decfddb
Time:	2023-08-13 05:51:25Z

I've checked all my licences, they're all good.

I've checked the data sources, they're all good.

I've done a manual refresh of the Dataset in Power BI Desktop, all good.

I have no idea what's going on here?

Any guru's out there?

[Alef_Ricardo_]

Hi Andre,

Thank you for reaching out to me. I'm sorry to hear that you're experiencing issues with the Power BI Service refresh. It seems like you have already raised a support ticket with Microsoft and provided them with the necessary details. That's a good step towards resolving the issue.

Based on the information you've provided, it appears that the issue is related to pre-existing legacy SharePoint lists in classic team sites that are empty, causing the Power BI Service refresh to fail using Implementation 2.0. You also mentioned that the same issue is occurring with power queries where the ID column has been removed and the list has data.

It's possible that there may have been some changes on the Power BI Service that could be causing this issue. I would recommend continuing to work with Microsoft support to resolve this issue as they would be best equipped to diagnose and fix any problems.

In the meantime, if you have any other questions or concerns, please feel free to reach out to me. I'm here to help in any way I can.

[MikeLach]

Same issue for us. All of our existing datasets that pull data from SharePoint online lists are having a problem refreshing in the workspace. Local refreshes done by hand in the desktop app work fine though.

[PEM406]

Same issue here, so I was very glad to find this thread.

I have two SharePoint lists with implementation method 2.0, neither of which are empty nor have they had any fields removed. Both still have the ID fields.

I can refresh the desktop version and republish succesfully but it's the dataset refresh in service that keeps failing with the same error. I've published under different names, into different workspaces and even rebuilt the queries and reports from scratch. The refreshes all work from desktop and re-publish successfully but the service refresh fails. 

"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. "

[sulleyinoz]

@PEM406  

That's interesting ... so you're suggesting it may be something to do with the legacy sharepoint lists rather than the empty or missing ID field in Power Query.

Hmmmm .... this would be true with my scenario as well, since we tested a brand new sharepoint list that was empty and it went through fine.

[patri0t82]

I'm experiencing the same problem since Aug 12 6:00AM refresh (ADT)

[Dauphinois]

Hi All,

Joining the club with the same issue impacting us.

The reports are running fine in the Desktop client, but they failed when published in the Portal.

Let's hope that Microsoft will provide us with a fix soon.

[Lexx]

I wish I had found this topic yesterday 😞

I am facing the same issue, nothing in the report has been changed since publication and suddenly I am getting the same error as everyone else.

Manually refreshing in Power Bi desktop works but any subsequent automatic refreshes fail.

I hope MS fixes this soon.

*edit:

This is the error I am getting

=======

Data source error Expression.Error: The column '<ccon>ID' of the table wasn't found.. ID. </ccon>. The exception was raised by the IDbCommand interface. Table: vn_main.
Cluster URIWABI-NORTH-EUROPE-H-PRIMARY-redirect.analysis.windows.net
Activity ID d603e819-0eba-4cc5-891e-a572aa2fc891
Request ID a129ef37-0528-4608-84b3-2e4bc02969f5
Time 2023-08-15 09:32:40Z

=======

[AlexGooding]

I'm getting the exact same issue here as well. Manual refreshes in Desktop is working, but similar to you as soon as publish to Service it fall over. Mine is also a Power BI Connection to SharePoint lists using 2.0.

[AustenE]

Can confirm this issue is occurring for us as well. Dataset refreshes fine on Desktop, showing identical refresh error in web. Seems to be at Microsoft end. Interestingly, we are using the same Cluster URI as OP:

• Cluster URI: WABI-AUSTRALIA-EAST-A-PRIMARY-redirect.analysis.windows.net
  

Coincidence?

EDIT: Even though the desktop app is allegedly refreshing, it appears to be missing the latest data from the source (a Sharepoint List)

[Anonymous]

I am also facing the similar issue, it started all of a sudden with Sunday morning(Sydney Time) 

Data source error: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: xxxxxxxxxxxxxx

Cluster URI:	WABI-AUSTRALIA-SOUTHEAST-redirect.analysis.windows.net
Activity ID:	e513f0c6-4221-4471-95b3-41da5ae373a5
Request ID:	e5e969ae-07c7-410b-844d-54ad808bb5a7
Time:	2023-08-12 22:24:26Z

The Table Name also changes with each of the scheduled refresh. 

The refresh works fine with the Desktop version. Not sure what's causing the issue. 

Current Workaround is to refresh the in desktop version and publish it again. But scheduled refresh fails again and again. I guess this is a widespread issue with Microsoft Fabric. 

Experts are welcome to comment. 

[v-rongtiep-msft]

Hi @sulleyinoz ,

Have submitted this issue internal to confirm, would update here as soon as possible if there is any update about it.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ashc_cool10]

Hi @v-rongtiep-msft ,

I am facing the same issues from last 3 weeks now. I have two data sets (One SAP HANA and One Sharepoint) .My report is getting refereshed in Desktop however when i am trying to refresh the report in service its getting failed.

I have checked both of the sources having same security level"Organizational" and i have updated my gateway as well. 

(Note-I have one founding that i have done merge between sharepoint and hana source and frpm that step its not refreshing the data. If i am not doing any merge between by data source and trying to refresh my data in PBI Service its happening.

Please see below screenshot of the error :

Please help if you have any idea what needs to be done.

Regards,

Ashish

[Anonymous]

Its better to delete the unnecessary references to these column IDs in the data transformation steps.

[sulleyinoz]

So, it seems it started to fail again as of the 9th September.

Same issue exactly as the original failure ... empty tables won't refresh and cause the entire refresh on PowerBI Service to fail.

Desktop works fine as usual.

Seriously Microsoft, I have lost a client over this refresh issue.

[MrSandsman]

I have also just had this exact same issue. Without any prompt or changes on our end, we started getting this same error on some of our datasets over the weekend.

I tried leaving the 'ID' column in my dataset, and that worked for removing the error for one table. But then I just got the same error on a different table in the same dataset. I tried the same approach on that table but it doesnt appear to have fixed the issue this time.

I presume Microsoft have made some sort of change and it's messed things up a bit. Does anyone have a solution that doesnt require me to re-build my dataset? 😫

[MrSandsman]

In case it's of any help to anyone in indentifying and fixing the issue. I've noticed that the only tables in my datasets that are having this refresh issue are the ones that have 'Choice' type columns in. All other tables (newer or older than those tables) that don't include 'Choice' type columns aren't having any trouble refreshing in the service.

[MrSandsman]

Further to the above - as many have identified, a short term fix of keeping the ID column in the table works around the issue.

I have also identified that anywhere you use the table causing the issue in a merge with another table, you also need to include the ID column (i have several instances of this). So i was able to completely resolve the issue by going through all the tables where the original table was included in a merge and keeping the ID column. This os obviously not ideal. But may buy you some time buy getting your flows running correctly until MS fixes the issue.

I will be lodging a ticket with MS today as suggested to help highlight the issue.

[sulleyinoz]

UPDATE

So, a ticket is raised with Microsoft, I spent an hour and a bit on a support call.  And so far we have determined the following:-

• Sharepoint List (classic teams site) to Power BI Dataset via Implementation 2.0 connector with empty list will create the error

BUT, a brand new empty list created in the same site works fine ... i.e. new one just created as empty, worked.

So it seems there is an issue with legacy lists that are empty with PowerBI Service refresh.

[mpwilliamson]

I'm getting the same across multiple tenants for reports refreshing since 9th August.

In my case a workaround is to not remove the ID column in Power Query and let it come through as-is to the data model.  It's usually not being used for anything on the reports I run so I've stripped it out of reports but putting it back in and publishing to service fixes that.  It only works if I bring through the actual ID column, I can't just rename another column to ID, so I guess it's trying to do something when it hands data from PQ to the data model and assumes the SharePoint ID column will be present?

[npotts]

Experiencing the same issue since Aug 12.  Desktop refreshes no problem, but Power BI Online throws the error 'The column '<ccon.ID' of the table wasn't found'.  The report that failed is using Implementation="2.0".  Saved a copy of the report, deleted and re-added the SharePoint Online list using Implementation 1.0 and the refresh was successful in Power BI Online.

Waiting to see if Microsoft can come up with a fix before tearning down and rebuilding many reports.

Also, the Fabric icon now appearing in Power BI Online.  Don't recall seeing it last week.   

[jfprend]

I have the same issues appearing in more than one report at the moment. I'm able to refresh both reports in desktop without any issues, and as far as I can tell the ID column isn't being used anywhere. I can confirm that the source tables in my reports all have data in them, and each source currently resides on SharePoint Online. Neither report has had this issue in the past, so I'm assuming something has changed on Microsoft's end. Please let me know if you or @sulleyinoz  find a solution!