topic Re: Refresh failed because this semantic model requires a gateway. How to avoid without gateway? in Developer

Refresh failed because this semantic model requires a gateway. How to avoid without gateway?

jaryszek — Fri, 25 Apr 2025 08:59:10 GMT

Hi,

I have 3 queries in power query created:

1. Result

let Result = if ParamEnvironment = "Customer" then Table.FromRows({}, {"RegionID", "RegionName", "RegionCode", "DataCenterLocation"}) else CSVSource() in Result

2. paramEnvironment

"Customer" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

3. CSVSource

let CSVSource = () => Folder.Files("D:\cost-mgmt-data1\CostManagement\") in CSVSource

All:

I disabled "Enable Load" option for CSVSource to be sure that it will not be loaded and refreshed in power bi service.
But still when referencing in Result query within if statement -> it is requiring me to refresh this source and use gateway.
As seperate query without any dependencies to other queries - it is working without any issue.
But seperate query without refering it from other queries make no sense.

----------------------------

I am getting error after publishing:

the thing is that local files should work only on desktop, i do not want to propagate to service and run gateway.

Goal:
Have my team working in power bi desktop environment using local files and after publishing report into customer tenant - switch datasources for customer's database without asking about gateway for CSV files...
In other words:
Using local dev files in Power BI Desktop, but not shipping them to Power BI Service - no gateway needed in production

How to make this working? Lambda function does not work...
Set up took from here:
https://community.fabric.microsoft.com/t5/Developer/2-datasets-or-parameters-to-upload-report-within-customer-tenant/m-p/4666606#M60745

Best,
Jacek

Re: Refresh failed because this semantic model requires a gateway. How to avoid without gateway?

lbendlin — Fri, 25 Apr 2025 12:01:38 GMT

Goal: Have my team working in power bi desktop environment using local files

I think that is a futile exercise. At a minimum place these "local" files on a SharePoint. That obviates the need for a gateway.

Re: Refresh failed because this semantic model requires a gateway. How to avoid without gateway?

jaryszek — Fri, 25 Apr 2025 12:42:21 GMT

So all this code from Microsoft :

let Environment = Parameter_Environment, Source = if Environment = "Team" then Csv.Document(File.Contents("ExcelPath")) else if Environment = "Customer" then Sql.Database("CustomerDBServer", "CustomerDB") else error "Unsupported environment" in Source

will not work anyway without gateway?

I heard also:

If you want to keep your approach, you have to disable the dev queries right before you publish the model to the web service.

it will not work if i have used If statement refering to any even disabled query because it will require to have gateway still... even on Customer Environment.

You can handle this scenario best by using a parameter in Power Query that controls which data source to use. Define a parameter like SourceMode with values such as "Dev" and "Customer". In your Power Query logic, use an if-statement that switches data sources based on the parameter's value. For example, if the parameter is set to "Dev", pull from your CSV or GitHub. If it's set to "Customer", connect to the customer’s SQL database. Before publishing the report to the customer's tenant, switch the parameter to "Customer" and make sure the queries using dev sources are either removed or disabled to prevent errors. Then publish with only the customer database source active. This way, the dataset remains refreshable on the customer side, assuming the CustomerDatabase is accessible through either a gateway or a direct connection. This approach avoids the complexity of managing two separate datasets and keeps everything in a single PBIX file with conditional logic handling the data source.

this is another solution from another power bi developer.

It seems that everybody advise to have swither and disabled queries but it seems to not be woring if even one query depends on this disabled one....

Best,
Jacek

Re: Refresh failed because this semantic model requires a gateway. How to avoid without gateway?

lbendlin — Sat, 26 Apr 2025 14:10:14 GMT

You are "fighting the API". You are trying to do something that the Formula Firewall is actively trying to prevent.

https://learn.microsoft.com/en-us/power-query/data-privacy-firewall

Re: Refresh failed because this semantic model requires a gateway. How to avoid without gateway?

v-sathmakuri — Wed, 30 Apr 2025 15:03:22 GMT

Hi @jaryszek ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

Re: Refresh failed because this semantic model requires a gateway. How to avoid without gateway?

v-sathmakuri — Mon, 05 May 2025 17:07:45 GMT

Hi @jaryszek ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

Re: Refresh failed because this semantic model requires a gateway. How to avoid without gateway?

v-sathmakuri — Thu, 08 May 2025 12:39:46 GMT

Hi @jaryszek ,

I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!