Forum Discussion

RafalGabin's avatar
RafalGabin
Regular Visitor
10 months ago
Solved

Refresh problem

Today after adding excel file from Sharepoint merged with data from MS SQL Server (data from MS SQL Server connected throught gateway on server) I noticed error when I wanted to refresh data: {"e...
Need Help
  • rohit1991's avatar
    rohit1991
    10 months ago

    Hii RafalGabin 

     

    That error appears because your dataset now mixes a cloud source (SharePoint) with an on-prem SQL Server (gateway) and the gateway is not configured to allow cloud data source refresh in mixed-mode queries. When you added the new Excel file and merged it with SQL Server, the model became a “combined” data source, and the refresh is blocked.
    To fix it, go to Gateway >> Settings >> Allow user cloud data sources to refresh through this gateway and enable it (or move the SharePoint file into a Dataflow so the dataset only refreshes from the gateway). Once the gateway allows cloud + on-prem mixing, your scheduled refresh will work again.

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
10 months ago

Hii RafalGabin 

 

That error appears because your dataset now mixes a cloud source (SharePoint) with an on-prem SQL Server (gateway) and the gateway is not configured to allow cloud data source refresh in mixed-mode queries. When you added the new Excel file and merged it with SQL Server, the model became a “combined” data source, and the refresh is blocked.
To fix it, go to Gateway >> Settings >> Allow user cloud data sources to refresh through this gateway and enable it (or move the SharePoint file into a Dataflow so the dataset only refreshes from the gateway). Once the gateway allows cloud + on-prem mixing, your scheduled refresh will work again.

  • Zanqueta's avatar
    Zanqueta
    Icon for Super User rankSuper User
    10 months ago

    Hi RafalGabin,

     

    I guess this error occurs because you are attempting to combine a cloud data source (SharePoint) with an on-premises source (SQL Server via Gateway) within the same dataset. 

     

    The message “Cloud data source refresh is not allowed in gateway setting” indicates that the SharePoint source is treated as cloud, while the model is configured to use the Gateway for SQL Server.
     
    How to resolve this
    There are two main approaches:
     

    Option 1: Configure all sources in the Gateway

    1. In Power BI Service, go to Dataset Settings > Gateway.
    2. Add the SharePoint connection to the Gateway (even though it is a cloud source, it can be configured as a gateway source).
    3. Ensure that:
      • The SharePoint URL used in Power BI Desktop matches exactly the one configured in the Gateway.
      • Credentials are correctly set.
    4. Associate both sources (SQL Server and SharePoint) with the same Gateway.

    Option 2: Avoid using the Gateway for cloud sources

    • If possible, remove the Gateway dependency for SQL Server by using Azure SQL Database or Dataflows.
    • Alternatively, keep the Gateway only for SQL Server and ensure that SharePoint remains a direct cloud connection without being forced through the Gateway.

    Best practices

    • For hybrid scenarios (cloud + on-premises), consider using Dataflows to load SharePoint data separately and then combine it within the service.
    • This reduces complexity and avoids refresh errors.

     

    Official documentation:
     

    If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

    Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.