Parameters for Source and Environment in Power Query

[PRV]

Hi all

I'm facing an issue when using Parameters in Power Query. 

I created 4 Parameters for my Snowflake Connection 

1. Environment (DEV, INT, PROD)

2. SF_ROLE (2 different roles)

3. SF_QUELLE_PUBLIC (which connects to my public schema and shows me all the views/tables in there)

3. SF_QUELLE_LOGISTIK (which connects me to my Logistik schema and shows me also all the views and tables in there)

Everything works perfectly fine, but even though I defined these data connection to DirectQuery, when connecting to my tables through the SF_QUELLE_PUBLIC & _LOGISTIK, it automatically switches them into Import mode. 

Is there a obvious reason for that, that I'm missing? 

This is the code of the advanced editor for one those connections:

let
Source = Snowflake.Databases("pistor.west-europe.azure.snowflakecomputing.com","CONSUMER",[Role=SF_ROLE]),
CONSUMPTION_Database = Source{[Name=ENVIRONMENT&"_CONSUMPTION",Kind="Database"]}[Data],
LOGISTIK_Schema = CONSUMPTION_Database{[Name="LOGISTIK",Kind="Schema"]}[Data]
in
LOGISTIK_Schema

Does somebody have had the same issue or something related and can help me with that?

PS: I think it has to do something with this error message, but why is it appearing?

Cheers

Valmir

[v-veshwara-msft]

Hi @PRV ,

May I ask if the solution provided has addressed your needs? If so, please consider marking it as Accepted Solution to help others with similar queries.

If you need any further assistance, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @PRV ,

We’re following up regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges. if you found other solution , please consider sharing it here to help the community with similar queries.

If you still need assistance, please let us know.
Thank you.

[v-veshwara-msft]

Hi @PRV ,

Thnaks for reaching out to Microsoft Fabric Community.

Just checking in to see if you were able to resolve your query. If not, please consider the following:

It appears the issue is rooted in how Power BI handles DirectQuery mode with dynamic parameters. Specifically, using expressions like ENVIRONMENT & "_CONSUMPTION" within the Snowflake.Databases() step causes Power BI to lose track of static metadata, which is required for DirectQuery to function correctly. As a result, it defaults to Import mode.

To maintain DirectQuery behavior:

Avoid metadata navigation steps like Source{[Name="..."]}[Data] when using dynamic parameters.

Instead, try using a native query with fully qualified table names:

let
Source = Value.NativeQuery(
Snowflake.Databases("pistor.west-europe.azure.snowflakecomputing.com", null, [Role = SF_ROLE]),
"SELECT * FROM " & ENVIRONMENT & "_CONSUMPTION.LOGISTIK.YourTableName",
null,
[EnableFolding = true]
)
in
Source

After loading the query, go to Query Properties and check “Don’t allow changes to connection settings” to lock it to DirectQuery.

If It Still Doesn’t Work
Try testing in Dataflows Gen2 in Microsoft Fabric, which can sometimes offer more control over parameter handling.

Alternatively, structure your query as a Power Query function that takes ENVIRONMENT and SF_ROLE as inputs rather than parameters hardcoded into steps.

If you're still stuck after trying the above, please let us know.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[v-veshwara-msft]

Hi @PRV 

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[Anonymous]

Hi, @PRV 

Based on the information you provided, the root cause of the problem is a compatibility limitation of parameterized connections with DirectQuery.
Power Query needs to get a list of databases (metadata) in the Snowflake.Databases() step first, and dynamically concatenating ENVIRONMENT &_CONSUMPTION causes Power BI to fail to precompile static SQL queries in DirectQuery mode. Metadata operations, such as listing tables/views, often force a switch to Import mode. The parameters SF_ROLE and ENVIRONMENT are passed dynamically during the join process, while DirectQuery requires that the data source connection parameters must be fully determined during the initial join step and cannot be dynamically calculated through subsequent steps. When you also expand the list of tables in the schema, Power Query generates an operation (implicit metadata query) that contains a Value.NativeQuery by default, which breaks the linear execution chain of DirectQuery.
You can try bypassing metadata enumerations and hard-coding database and schema names directly, controlling only the roles and environments via parameters:

let
Source = Snowflake.Databases(
"pistor.west-europe.azure.snowflakecomputing.com",
ENVIRONMENT & "_CONSUMPTION", // Advance the database name to the join layer
[Role = SF_ROLE]
),
LOGISTIK_Schema = Source{[Name = "LOGISTIK", Kind = "Schema"]}[Data]
in
LOGISTIK_Schema

Or enable native query mode to force the use of Native Query to pass SQL directly in Power Query. To create a blank query, enter the following code:

let
Source = Value.NativeQuery(
Snowflake.Databases(..., [Role = SF_ROLE]),
"SELECT * FROM " & ENVIRONMENT & "_CONSUMPTION. LOGISTIK. YourTableName",
null, [EnableFolding = true]
)
in
Source

Make sure that all subsequent steps don't add actions that can't be collapsed, such as custom columns

In Power BI Desktop, right-click the query → properties → check Don't allow connection mode changes. Once you've tried the workaround, you'll also need to check if the mode is DirectQuery.

You can check the following links:

DirectQuery in Power BI - Power BI | Microsoft Learn

Connect to Snowflake with Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PRV]

Thanks for the explanation and the given options, but the problem is not solved yet.

[Deku]

You could try using TMDL view to swap the connection type to direct query and see whether that works as a debugging step

[PRV]

No, that doesn't work