Error We cannot convert the value \"\" to type Table. in PowerBI service but it runs OK in Desktop

[HariH]

Hello, 

I am struggling with and error that only occurs when I publish my report in PBI Service but does not occur when I refresh it in PowerBI desktop so I can not figure out what is causing it. I have carefully checked any columns that contain tables and I do not see a mix of tables and text. The tables are empty for the most part but that should not be a problem. Any ideas what may be the cause and potentially solution to this? Here is the full text of the error: 

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"We cannot convert the value \"\" to type Table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.Value","detail":{"type":1,"value":""}}],"exceptionCulprit":1}}} Table: Core Action Items.
Cluster URI: WABI-US-EAST-A-PRIMARY-redirect.analysis.windows.net
Activity ID: 25d21776-4c49-49b4-886c-226ec3f07b9b
Request ID: 1b22f3a1-0a01-e487-11ee-3753625bf06c
Time: 2023-11-20 14:42:03Z

Thanks in advance

[HariH]

Thanks @arvindsingh802  but in my column to expand there is no mix of data types, all of the rows have Table in them, no text mixed in. The data is not much so I was able to inspect every row. 

[mardejj]

Hi @HariH ,

Did you find the way to solve it? I have exactly same error. It works in Desktop  but not in PBI Service.

Thanks 🙂

[HariH]

Hi @mardejj not unfortuantely 

[arvindsingh802]

Just wanted to confirm

1. Are you using same user account to refresh the data on desktop & service ?

2. Are you merging data from two different sources?

[HariH]

Indeed I use my account to refresh the data on desktop and service. Also I do merge data from different sources. First I have a merge of one excel sheet data with another excel sheet data and following that with data that comes form sharepoint list via v2.0 connector. 

[arvindsingh802]

as you are combining data, this could also be beause of data privacy settings, please make sure the privacy settings are same for both the sources
You may need to enable "Fast Combine" as well.

[HariH]

Thanks @arvindsingh802 I made sure that all my sources are set to Organizational data privacy level both in the Desktop before publishing and in the PBI Service. The issue still perisists unfortuantely. I am not sure how to enable fast combine, I do not seem to have query options under my options and settings and also I do not want to ignore privacy settings completely. As far as I understand it as long as the soruces all have same privacy setting they should combine just fine. 

[arvindsingh802]

Turning Fast Combine on will ignore the private setting and allow the execution to occur.
Please refer https://support.microsoft.com/en-us/office/set-privacy-levels-power-query-cc3ede4d-359e-4b28-bc72-9b... 

[HariH]

Thanks @arvindsingh802 I have reviewed this article following you previous post but as I mention above I do not have "Query options" in "Settings and options" it may be a access level restriciton, not sure and from the Dataset Options I see I can only change the Privacy level, nothing more. 

[arvindsingh802]

Fast combine means Ignoring the Privacy levels
Please follow this - 
File ->Options and Settings -> Options -> Current file -> Privacy
Select Ignore the Privacy levels and potentially improve performance

[HariH]

Thanks @arvindsingh802 well this changed the error message, once I changed the privacy settings everywhere to none and chose to ingore them as per your guidance. But still no luck with getting the data to refresh. 

• Data source error{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Information is needed in order to combine data"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"File\",\"path\":\"C:\\\\Users\\\\a230587\\\\OneDrive - Syneos Health\\\\Desktop\\\\Projects\\\\UCB affiliate tracking tool\\\\Affiliate WF tracker monitoring\\\\3hourly report.xlsx\"},{\"kind\":\"File\",\"path\":\"C:\\\\Users\\\\a230587\\\\OneDrive - Syneos Health\\\\Desktop\\\\Projects\\\\UCB affiliate tracking tool\\\\Affiliate WF tracker monitoring\\\\NamesTable.xlsx\"},{\"kind\":\"SharePoint\",\"path\":\"https://synh.sharepoint.com/sites/SafetyManagement\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacySetting"}}],"exceptionCulprit":1}}} Table: Core Action Items users list.
• Cluster URIWABI-US-EAST-A-PRIMARY-redirect.analysis.windows.net
• Activity ID8db1e36d-ef30-4b40-b98d-ee9bbf0329d6
• Request ID7888be23-3076-c2f6-d2a5-ed653a8c47df
• Time2023-11-24 10:09:03Z

[arvindsingh802]

Please keep the privacy level as organizational at data source level in Power BI Desktop & Service, this error is because of that only

[arvindsingh802]

Please refer below threads
https://community.fabric.microsoft.com/t5/Desktop/Cannot-convert-value-to-type-table/m-p/557584

https://community.fabric.microsoft.com/t5/Desktop/Cannot-convert-value-to-type-table/m-p/288137