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

[jaryszek]

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

Best,
Jacek

[v-sathmakuri]

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 · Accepted solution]

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]

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]

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

[lbendlin]

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]

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.