Forum Discussion

mpros's avatar
mpros
Helper I
1 year ago

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

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 des...
Need Help
Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

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's avatar
mpros
Helper I
1 year ago

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's avatar
    lbendlin
    Super User
    1 year ago

    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's avatar
      mpros
      Helper I
      1 year ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.