Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
jaryszek
Post Prodigy
Post Prodigy

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

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:

jaryszek_1-1745568084087.png
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:

jaryszek_0-1745567886872.png



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...

Best,
Jacek

1 ACCEPTED SOLUTION
v-sathmakuri
Community Support
Community Support

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!!

View solution in original post

6 REPLIES 6
v-sathmakuri
Community Support
Community Support

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!!

v-sathmakuri
Community Support
Community Support

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!!

v-sathmakuri
Community Support
Community Support

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!!

jaryszek
Post Prodigy
Post Prodigy

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


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

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.