There was an error when processing the data in the dataset from SharePoint

[cisaemp]

Hello!

I have a Power Bi report getting data from SharePoint.  Everything works fine in Power Bi Desktop, but this weekend the automatic refresh from Power Bi Service has an error when processing the data in the dataset, See below:

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: Jobs.
Cluster URI: WABI-US-EAST-A-PRIMARY-redirect.analysis.windows.net
Activity ID: 5e5aef79-1a88-42a8-8616-de24ac636707
Request ID: f6e2f053-24e8-c5f7-4ae4-42f2a7a101d0
Time: 2023-08-15 16:11:50Z

Please any help!

[v-xinruzhu-msft]

Hi @cisaemp 

The issue has been reported, please wait for the feedback.

Best Regards!

Yolo Zhu

[Bhaskarkolli9]

Hi Thanks for the update,

Explaining the issue again power BI report refresh is working fine in desktop same report when published to service it is not working.

Report is contains Share point folder table and SAP HANA view we have indentified the issue is after merging the these 2 table refresh is not working in service(but desktop report refersh working fine after merging). with out merge report is working fine in service as well.

[v-xinruzhu-msft]

Hi @Bhaskarkolli9 

The connection problem of sharepoint in Power BI Desktop will be fixed in the new version in November and the situation of Power BI Service will be fixed in December, Please waiting for back!

Best Regards!

Yolo Zhu

[PBI_Queryous]

Im experiencing the exact same issue for the same ID column.

This was working fine, until last week. Something is deeply broken in SharePoint / PBI Service. PowerBI Desktop works fine. However, PBI Service says it cannot find the ID column, when it is 100% there, and isn't being referenced or hardcoded in the M-code.

Republishing to other workspaces does not solve the problem. This is a MSFT glitch. Could someone help please? 

[MikeLach]

This same problem started for us on Friday evening as well. The ID column is there in the SharePoint Online list, it is there in the data after the import, and any refresh done of the dataset in the desktop app works successfully. However, after publishing the dataset to our workspace the refreshes fail with the error mentioned above.

[PBI_Queryous]

I'm having the same problem. PBI Service is lying to us. The ID column exists, and PBI Desktop refresh works fine. The PBI Service claims it can't find the ID column, even though this column isn't referenced in the M-code.

This is definitely a MSFT bug.

[aj1973]

@PBI_Queryous 

The solution is here in the message  7 of 11, read it please 

[ashc_cool10]

Hi @aj1973 

i am facing the same issue. Untill Friday in PB Service my refreshes were happeing but suddenly it stopped working. I have report with two data source(SAP HANA and SharePoint-file is in csv).

My data set is getting refreshed in Desktop however when i am trying to refresh this in service i am getting below mentioned error.  

I have checked all the security  is "Organisation" in both of the data source . Please help if you have any idea.

[aj1973]

Hi @ashc_cool10 

Did you take a look at message 9  and 14 ?

[PBI_Queryous]

Thanks @aj1973 - 

It seem's you need to change the ViewMode to "All" instead of "Default".

Also - you MUST select the ID column, for some reason it doesn't let you unselect this column. I don't recall this being a limitation before?

Kindest regards,
PBI Queryous

[aj1973]

@MikeLach @cisaemp 

Could you guys republish the dataset into another workspace and try again ? 

This thing is indeed weired !

I tried it on my tenant and it is working fine. 

[cisaemp]

Yes, I republished the dataset into another workspace, but didn't work.  I tried the following and worked:

In the first step of the query, Source, I selected the following settings to bring all the columns from the SharePoint View. Before, I added the ID column in the SharePoint View of the list.  The other option, All - Retrieve all Columns works too and you don't need to add the ID column to the View.

Then, I choose the columns that I need for the report including the ID column.

Then, Close & Apply the query and Publish. I tested in the Power Bi Service and started to work and I activated the Schedule Refresh. 

Thanks all for your help!

Edgar

[aj1973]

Well done, I now remember that step. I had to do it for a client few months ago but couldn't recall it for this thread.

Good job @cisaemp 

[MikeLach]

@cisaemp stated: "The other option, All - Retrieve all Columns works too and you don't need to add the ID column to the View."

This does not work for me - I have always been using the "All Columns" option from the beginning before this problem started.

[MikeLach]

Published to a personal workspace and the same issue exists.

[aj1973]

Hi @cisaemp 

Open Power Query in the desktop and check out the Query folding steps. Sure you will identify the problem. Besides check out what has changed in Sharepoint.

[Bhaskarkolli9]

Issue only after merging SAP HANA VIEW WITH SHARE POINT Schedule refresh is getting failed with out merge refresh is working fine in service. If we merge both refresh failing in service and working fine in desktop please suggest.

[aj1973]

@Bhaskarkolli9 

You need to Open Power Query and check out the steps. But before you need to read message 9 and  14 .

[ashc_cool10]

Hi @aj1973 ,

Firstly we are using Sharepoint Folder instead of Sharepoint Online Services and in point 9 &14 its shows steps for "Sharepoint Online Services"

Below is the screenshot of my folder path :

 

Then we chooses the file which is availble in the folder :

after transforming the data we are doing merge between SAP Hana data source and this WMS-REELID(file).

As soon as we do the merge between these two files sechdule refresh is not working however if i am not doing Merge and trying to refresh the data it's happening.

So not sure what is wrong with it . Request you to please help !

Regards,

Ashish

[aj1973]

@ashc_cool10 

I would have to test it to tell you what's wrong. But in PQ try to refrence the sharepoint file and merge the refrenced file with Hana and see if it works.