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

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

sulleyinoz — Mon, 09 Oct 2023 01:34:02 GMT

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

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?

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

avinaash — Sun, 13 Aug 2023 16:03:13 GMT

The error message means, the column "ID" has been removed from one of the table that you were using.
If you are not using that column, download the PBIX file and just refresh it in your desktop and upload it again; That should work.
All the best.
Please mark this as answer if this worked.

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

Wojnar — Sun, 13 Aug 2023 21:20:39 GMT

We are facing the same refresh error in our organization. I assume this is a global issue ...

I resolve that in the following way:

You have to set your ApiVersion to 15. Of course, it cannot be so simple because the table's structure will be slightly different afterward.

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

sulleyinoz — Sun, 13 Aug 2023 22:12:21 GMT

Hi avinaash

I wish it were that easy, but nothing was removed - especially the ID column, which you cannot remove from a SharePoint List datasource.

It definitely seems #Microsoft has done something here - otherwise it is unexplainable?

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

sulleyinoz — Sun, 13 Aug 2023 22:49:02 GMT

Hi Wojnar

I'm using the new implementation method 2.0 which has been wonderful and fast these last 6 months. Is this going to destroy that?
When you say set the ApiVersion to 15, where are you doing that? For every table in the Source script? I have 93 tables, I don't fancy doing that.

I've also checked every table still has its ID column, so this is definitely something else going on here.

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

sulleyinoz — Mon, 14 Aug 2023 01:44:24 GMT

Sorry, I meant to say the first thing I did was a dataset refresh using Power BI Desktop - it worked fine

I re-published the dataset, and the next Power BI Service refresh failed with the same error.

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

sulleyinoz — Mon, 14 Aug 2023 01:43:16 GMT

Could it be possible that Microsoft have suddenly changed something ...

I just did a test in PowerBI ... added some data to an empty table ... and it went through (as in that table no longer appeared in the error message but the next empty table did). And to be clear, it still means the Power BI Service Refresh fails.

So it appears only my empty tables are throwing up with this error.

Is there a global flag to not error on empty tables?

And every time I run this it comes up with a different 1 of my 93 tables that has the error. Definitely not the dataset or the data, this is something in PowerBI. My next step is to publish the same Dataset under a new name into PBI Service and see if that works.

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

sulleyinoz — Mon, 14 Aug 2023 06:12:50 GMT

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.

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

mpwilliamson — Mon, 14 Aug 2023 09:33:16 GMT

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?

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

sulleyinoz — Mon, 14 Aug 2023 11:25:03 GMT

That sounds interesting .... for me ALL my ID fieds are still in the PBI Dataset and were never removed, but the timing is interesting. Around the same time something has definitely changed. Thx for that.

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

mpwilliamson — Mon, 14 Aug 2023 11:27:49 GMT

Ahhh after I confidently stated that would fix it I've come across a report where it doesn't so now I'm more confused than before!

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

sulleyinoz — Mon, 14 Aug 2023 11:30:14 GMT

Check if its empty tables or tables with data ... that's the issue I'm working through with MS. All my tables withe Data all refresh fine. But one single empty table and it blows up and the refresh fails. And it works on Desktop, but fails on PBI Service.

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

Anonymous — Mon, 14 Aug 2023 13:02:08 GMT

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!

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

npotts — Mon, 14 Aug 2023 21:13:39 GMT

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.

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

Anonymous — Mon, 14 Aug 2023 22:36:17 GMT

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? 😫

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

Anonymous — Mon, 14 Aug 2023 22:52:40 GMT

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.

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

Anonymous — Tue, 15 Aug 2023 01:05:19 GMT

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.

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

AustenE — Tue, 15 Aug 2023 03:12:12 GMT

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)

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

Anonymous — Tue, 15 Aug 2023 03:39:46 GMT

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.

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

Anonymous — Tue, 15 Aug 2023 03:45:27 GMT

In my case none of the table is empty. But still facing the refresh issue on the online but working fine on the desktop version

topic Re: Power BI Service Dataset Refresh Error - Expression.Error: The column '&lt;ccon&gt;ID' of the ... in Service

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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