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?

[Nilupul]

I also faced the same issue. My problem was in one of the table steps in the Power Query editor.

error :

Issue in power query editor:

So, if you encounter this issue, make sure to check the steps in the Power Query editor as well. This could help resolve the problem.

[Taryn]

The problem happens for me in the desktop as well. At this point, I can't even choose the SharePoint list because if fails at that step (Navigation). This issue seems tied to the age of the list. I created a new list by copying an existing list and it doesn't even allow me to remove the ID column because it fails on the navigation step. I get the error immediately when I select the list name.

However, when I create a new blank SharePoint list, I don't have the problem.

If someone could please let us know how we could change the old list to make it work, that would be wonderful. I really don't want to spend hours recreating every list and I don't want to disrupt ongoing activities.

[sulleyinoz]

**********  9th SEPT 2023 UPDATE - Now failing again with same issues **********

I don't know why I can't re-edit the original post - but here goes anyway.

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.

[sulleyinoz]

Oh Dear @Microsoft ... what have you done.  My refreshes started failing again Sat night.  Same issue, same expression.

Looks like some fool has gone and deprecated the code they thought was working ... AGAIN!!!  Seriously MS ... come on!!

[v-rongtiep-msft]

Hi @sulleyinoz ,

 The issue has been resolved. Please try refreshing.Thank you all for your understanding.

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.

[sulleyinoz]

@v-rongtiep-msft Please STOP marking this as the soluiton ... it is not the solution and is not resolved.  I have re-raised this with Microsoft on my old ticket number and awaiting a response.  

[mpwilliamson]

This issue is back again, although it only appears to be affecting empty lists, lists with data in are taking much longer to refresh, to the point that refresh is timing out completely on reports that previously refreshed successfully pre-4th September.  https://community.fabric.microsoft.com/t5/Issues/Service-quot-The-column-does-not-exist-in-the-rowse...

[MrSandsman]

Yes. I've noticed empty lists are now throwing an error again (including in the desktop application refresh). Not really a helpful comment from me. I dont have an answer for you. I'm just confirming you're not alone.

[sulleyinoz]

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.

Cheers

Andre

[jfprend]

Just wanted to post my update

I was able to get the error to go away for my workbooks by ensuring that the ID column coming from my SharePoint Online lists were included in the data models for each report (even though the ID field isn't used anywhere). Each time I got ID error I went through the affected table in Power Query and let the field flow through. Eventually this led to successful refreshing. 

Add'l info for context

- the affected workbooks contained various data source connections to SPO, excel and even a DB. The only tables I had issues with were the ones connected to SPO. 

- Both SPO connections use implementation 2.0

- There were no recent credential changes or report enhancements that would have changed my workbooks

Let me know if I can help at all!

[MikeLach]

What needs to made clear is that this is only a workaround and not a fix - Microsoft still needs to address the root cause of this problem. 

[jfprend]

Agreed

[mpwilliamson]

https://community.fabric.microsoft.com/t5/Issues/Power-BI-Service-Failing-to-refresh-SharePoint-list... 

I posted about this thread in the Issues forum and they are currently testing a fix for deployment according to the MS rep on that thread.

[patri0t82]

I can confirm for myself the issue was easy to resolve, as others have said. In my case it was a matter of modifying my "Remove Other Columns" step, and including "ID". That's all it took. Once I republished I was able to refresh.

[Mark_]

I have had exactly the same issue since Saturday but with a new SharePoint list! we are not using SharePoint Classic.  The fix seems to be as someone above also suggested:

• Make sure the ID column is visible on your SharePoint list in Sharepoint and at position 1 from the left.
• In the Power BI Query editor, you will find a step has been added to remove the ID column, remove this step or reference to the ID column from this step so that it is not deleted from your data model for any SharePoint list.
• Republish your report and refresh should work again for now!

Hope this helps, although very annoying that MS has obviously made a change that has a significant impact without understanding the implication of the change made before implementation.

[PEM406]

I agree, this also seems to have worked for me.

I'm using Sharepoint online lists and merging data from them both. I've just added the ID from both tables back into the query. This seems to work; at least triggering a manual refresh after publishing has worked when previously it had failed every time. 

As I'm merging the data from two lists, most rows then retain the ID from the primary list but in some cases I have rows that only appear on the secondary list. In those case I've multiplied the ID in the secondary list by 1000 to avoid the risk of duplication and then used a conditional column to merge the IDs from the two tables. I then deleted the original ID fields and renamed the conditional column to ID so there's only one ID field in the published query/report. 

As mentioned, annoying that a change like this wasn't announced and we've had to re-do work to resolve it.

[Dauphinois]

As Microsoft has not proposed a solution so far I have implemented the workaround that was indicated.

I have reviewed my impacted reports and added back the column "ID" to all SharePoint Lists (remove it from being removed). Then I have republished these reports.

Happy to report it works now.

[sailorman]

Same issue: https://community.fabric.microsoft.com/t5/Service/Expression-Error/m-p/3380786#M203174

[sulleyinoz]

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.

Thank you @Microsoft

Cheers

Andre

[deepthiv]

Looks like the issue has been fixed by Microsoft, auto refresh started working now without making any changes to the model.