Refresh Error in Power BI Service but not in Power BI Desktop

[PowerBIFin]

I have a report which successfully refreshes in the Power BI Desktop however it fails with the error below when it's published in the Power BI service. I'm unable to find out which query or table or function is causing the error just from the message below. Any ideas how I can find it out?

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 \"[Value]\" to type Record."}},{"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":"[Value]"}}],"exceptionCulprit":1}}}
Cluster URI: WABI-AUSTRALIA-SOUTHEAST-redirect.analysis.windows.net
Activity ID: ce13f4f5-17f8-4151-a5d2-cb43a056e19d
Request ID: 80239923-0d80-53f9-7c10-374302fb4637
Time: 2022-01-19 20:49:22Z

[lbendlin]

We cannot convert the value \"[Value]\" to type Record.

Check your Power Query code for assumptions that your data will always result in records. It doesn't. 

[PowerBIFin]

I have a number of tables/functions and has gone through each of them in Power BI Desktop and none of them shows the error in Power Query. All tables successfully display the Preview and load the data. This error appears only in Power BI Service but I can't seem to find out what's causing it or at the very least narrow it down to specific table.

[bcdobbs]

Is the dataset refreshing through a gateway?

Is it possible different credentials are being used by the gateway?

[PowerBIFin]

The gateway has a different credentials from my Power BI Desktop. The report used to be working in the PBI Service before and I haven't changed (add/remove) any data source yet from the report. All other reports using the same data sources are all working.

[v-jayw-msft]

Hi @PowerBIFin ,

Check if there's any error message on the data source under Manage Gateway.

Try using the credentials in Power BI Service to connect the data source on Power BI Desktop then check if there's any error in Query Editor.

Best Regards,

Jay

[PowerBIFin]

Thanks for your reply. All Gateway data sources have no error and are all working as evidence by other reports refreshing successfully using the same set of data sources. 

In order to troubleshoot, i have recreated a few tables in Dataflow and replicated the issue in one of my functions. This function returns value successfully in the 'Dataflow preview data' view and when i force it to 'Keep Errors' while in Preview View - it still doesn't return any error. The error is only encountered when refreshing the dataflow - same exact rows that returns the correct value in the Preview fails in when I refresh it from the service.

{"type":1,"value":"We cannot convert the value \"[Value]\" to type Record."}

 I found similar issue online due to PQ firewall but has anyone else encountered this?

https://social.technet.microsoft.com/Forums/en-US/bee8a8e3-c8bd-48c6-a2c3-5d00bc5031b1/expressionerror-cannot-convert-the-value-quotfunctionquot-to-type-function?forum=powerquery

[bcdobbs]

The other option is to remove the tables one at a time until it works. That way you can narrow the issue.