Forum Discussion
Refresh Error Cloud and On Premise
I am refreshing a semantic model that has a cloud data source which is merged with an internal Excel file. Both are fine in the gateway but I get the below error. I have followed what I believe are the steps to make this work, but it is still not working. Any suggestions?
Data source error: {"error":{"code":"Premium_ASWL_Error","pbi.error":{"code":"Premium_ASWL_Error","parameters":{},"details":[{"code":"Premium_ASWL_Error_Details_Label","detail":{"type":1,"value":"Cloud data source setting is not allowed refresh through gateway."}}],"exceptionCulprit":1}}} Table: DIM Jobs.
Hii DPCCGF
I find the issue as this please try the relevent solutions given as per the Microsoft learn docs:
This error is that Power BI is preventing the refresh of a cloud data source through an On-Premises Data Gateway, which typically happens when you're trying to combine a cloud source (like SharePoint Online, Azure SQL, etc.) with an on-premises source (like Excel on a local drive or network) in the same semantic model and attempting to refresh it using a gateway.
Some Solutions you can try are as (Multiple given which works for you):
1. Move Both Sources to the Same Environment
To ensure compatibility:
-
Either move the Excel file to a cloud location (e.g., OneDrive or SharePoint Online), OR
-
Use only on-prem sources and handle both through the gateway.
2. Use Dataflows to Separate Refresh Paths
-
Create Power BI Dataflows for either the cloud or on-prem source.
-
Let the Dataflow handle cloud-source refreshes (as it refreshes in the cloud directly).
-
Then use the result in your semantic model and avoid mixed-mode gateway refresh.
3. Use Import Mode + Power Query to Decouple
-
Load the sources separately using Import mode, and avoid merging cloud and on-prem sources directly in Power Query.
-
Use intermediate tables and relationships instead of direct merges within Power Query.
If you need further help let me know.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!-
5 Replies
Hii DPCCGF
I find the issue as this please try the relevent solutions given as per the Microsoft learn docs:
This error is that Power BI is preventing the refresh of a cloud data source through an On-Premises Data Gateway, which typically happens when you're trying to combine a cloud source (like SharePoint Online, Azure SQL, etc.) with an on-premises source (like Excel on a local drive or network) in the same semantic model and attempting to refresh it using a gateway.
Some Solutions you can try are as (Multiple given which works for you):
1. Move Both Sources to the Same Environment
To ensure compatibility:
-
Either move the Excel file to a cloud location (e.g., OneDrive or SharePoint Online), OR
-
Use only on-prem sources and handle both through the gateway.
2. Use Dataflows to Separate Refresh Paths
-
Create Power BI Dataflows for either the cloud or on-prem source.
-
Let the Dataflow handle cloud-source refreshes (as it refreshes in the cloud directly).
-
Then use the result in your semantic model and avoid mixed-mode gateway refresh.
3. Use Import Mode + Power Query to Decouple
-
Load the sources separately using Import mode, and avoid merging cloud and on-prem sources directly in Power Query.
-
Use intermediate tables and relationships instead of direct merges within Power Query.
If you need further help let me know.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!-
Hi DPCCGF,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay PabbuHi @DPCCGF,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay PabbuHi @DPCCGF,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay Pabbu
Hi,
we face the same issue and usually we can use one of the workarounds that are recommended above.
However, I got a request where the user cannot create different seperate dataflows for cloud and on premise data.The user runs a SQL query on an internal Oracle database (on premise) and passes a SQL parameter to the query that comes dynamically from a SharePoint list (cloud). He cannot run both sources independently, since the output of the SQL query would be too large and needs to be filtered before refreshing the dataflow.
He built a workaround using PowerAutomate. However, the required feature is a premium one and he wants to avoid paying for it.
Does anyone might have another solution or does Microsoft know about the issue and might fix it in near future?
Thank you and KR
Tina
