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. @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?

[sulleyinoz]

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.

[mpwilliamson]

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!

[sulleyinoz]

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.

[Anonymous]

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

[sulleyinoz]

@Anonymous Add your default SharePoint IDs back in (i.e. don't remove them in Power Query) and hopefully that fixes your issue if your tables contain data.

[sulleyinoz]

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.

[Anonymous]

I beleive this is a widespread issue, something has suddently changed at Microsoft's end, in terms of how the refresh deal with a Sharepoint List.

[sulleyinoz]

Would be great if everyone having the issue can rais a ticket with microsoft and refer to this thread too.

I've spent nearly 4hrs troubleshooting with Microsoft, doing single table tests, single empty legacy table tests, single table new tests, gateway tests etc ... all to no avail to fix the issue yet.

I haven't followed up with them this afternoon, but will do so shortly to check on progress.  They need to know this is much wider than just me.  I just happened to spot it overnight on the first night it happened.

Cheers All

[AlanCruz]

Hi everyone, I was facing the same problem.
My query uses the 2.0 implementation, and the ID field was not loaded in the query steps.
I changed my 'other columns removed' step so that the ID field was not removed and this solved my problem.

Thanks @sulleyinoz and all for the help.

[sulleyinoz]

That's great and good proof that with the ID field included in the Power Query and with data in the list, it should work and go through the refresh!

[npotts]

Thanks for posting the solution AlanCruz!  Changed the step to un-remove the ID column (2.0 implementation) and the refresh now works in the browser.

[avinaash]

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.

[BridgeFour]

This could be totally unrelated, but I did have similar symptoms (I could refresh on my desktop, but not in the cloud workspace).  For some reason my Data Source Credentials (in the cloud workspace) were changed from Organizational to None.  Once I changed them back, my refreshes started working again.  Just a shot in the dark.  Maybe it can help.

[sulleyinoz]

I'll double check, but I've redone the credentials multiple times during the tests with the MS Customer Service Rep, so doubt that's this main problem. Cheers

[deepthiv]

Experiencing same issue since Aug 12th. Any clue so far on the fix without making major change to the model? 

[sulleyinoz]

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.

[sulleyinoz]

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?

[Wojnar]

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.

[sulleyinoz]

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.