Forum Discussion
Power BI Service Dataset Refresh Error - Expression.Error: The column '<ccon>ID' of the ...
--------- 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 '...
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?
60 Replies
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 _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
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
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=======
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.
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.
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.
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
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.
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?
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.
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.
I'm experiencing the same problem since Aug 12 6:00AM refresh (ADT)
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. "
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.
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.
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.
