Service Gateway Updates Fails while working in Desktop (Service settings are ok)

[mpros]

Hello,
I've created a custom function that retrieves dynamically queries values from a sharepoint excel file and execute in Oracle DB creating a custom column in power query, which works fine in desktop. The problem is that even if the gateway is setup correctly, the updating job goes in error:

Errore

Errore dell'origine dati: {"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":"Web: Request failed: .../Documents/Template_queryxlsx_xlsx/_api/contextinfo"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"Web"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":".../Documents/Template_queryxlsx_xlsx/_api/contextinfo"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: Accoda1.

I've ben trying to map excel data source both on cloud and on premise-gateway obtaining same result.
Any Ideas?
Thanks

[mpros]

It appears that the problem derives for privacy level can you confirm?

Power BI prefers "staging" queries which acceess data sources directly, then a separate query to combine them.

It's about privacy levels - combining a direct source connection with another query makes it hard to determine privacy level

[Poojara_D12]

Hi @mpros 

Your custom function works in Power BI Desktop but fails in the Power BI Service due to a gateway issue when accessing the SharePoint Excel file.

Key points:

1. Error Cause: The gateway cannot access the SharePoint data source, returning a DataSource.Error.
2. Root Problem: Likely related to authentication or incorrect configuration of the data source in the Power BI Service.
3. Tried Solutions: Mapping the data source both to cloud and on-premises gateways gave the same error.
4. Recommendations:
   • Verify that the SharePoint data source credentials in Power BI Service are correctly configured.
   • Ensure the gateway is set to support cloud sources like SharePoint.
   • If using OAuth authentication, confirm it’s supported and configured properly in the gateway and Power BI Service.

Further troubleshooting might involve testing direct access to the SharePoint file from the gateway machine.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[mpros]

Dear Poojara_D12 ,
Credentials and OAuth2 configured correctly in gateway and cloud; I have so many models running on top of that that gets updated by service job consistently:

It only occurs with combination of custom function that queries dynamically on Oracle DB
Ciao

[lbendlin]

This issue is usually caused be merging a cloud data source with an on-premise data source. The "solution" is to add a gateway connection for the cloud data source. Yes, crazy. And irreversible too. Try at your own peril.

[mpros]

Dear,
have already tried to add a gateway connection for cloud ds. Doesn't work either. 
Ciao

[Anonymous]

Hi @mpros ， hello lbendlin  and Poojara_D12, thank you for your prompt reply!

You can use the on-premises data gateway to merge or append on-premises and cloud data sources in the same query.

Please try to select the gateway you want to configure, and select Settings from the top ribbon.

Under Settings, select Allow user's cloud data sources to refresh through this gateway cluster, then select Save.

More information for your reference:

Merge or append on-premises and cloud data sources - Power BI | Microsoft Learn

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mpros]

Dear @Anonymous ,
Gateway is already set up as you were mentioning:

Error in update still occurs.
Ciao

[lbendlin]

For file sources on Sharepoint use "SharePoin Folder", not "Web".

Scheduled refresh may still fail due to formula firewall restrictions.

[mpros]

Dear  lbendlin, 
I have many other flows working correctly with web connection. May it be the combination of the two?
I'll give it a try with sharepoint folder, and update you.
Ciao

[mpros]

No luck trying it with sharepoint connection, same error:

Ciao