How to Use Dynamic Query Parameters to Switch Source Query Connections in Power BI

How to Switch Source Query Connection Strings in Power BI with Dynamic Query Parameters

Scenario:

To manage multiple data or query environments such as Dev, Test, and Prod often requires maintaining separate connection strings in Power BI. 

Rather than hard‑coding server, database, and schema names, you can centralize these details in an environment table and control them dynamically through a parameter (e.g. p_Query_Env with values like Dev, Test, and Prod). 

With this setup, switching between environments becomes as simple as changing the parameter value, while Power Query automatically selects the correct server, database, and schema. This provides a clean, scalable solution for environment management that minimizes manual effort and ensures consistency across deployments.

We can achieve the above Scenario, as discussed below.

Step1: Define an Environment Variables Table

To enable dynamic source data environment switching in Power BI, we need to create a dedicated Environment Variables Table like below. This table acts as a central repository for all connection details across your environments (Dev, Test, and Prod).

Info_Query_Environment:

let

    /* Define Data Environment variables table with respective connection details */

    _Env_Table = 

        #table(

            {"Source_Env", "Server_Name", "DB_Name", "Schema_Name"},

            {

                {"Dev",  "Dev_Server01",  "Dev_DB",  "Dev_Schema"},

                {"Test", "Test_Server01", "Test_DB", "Test_Schema"},

                {"Prod", "Prod_Server01", "Prod_DB", "Prod_Schema"}

            }

        ),

    _DataType = Table.TransformColumnTypes(

        _Env_Table,

        {

            {"Source_Env", type text}, {"Server_Name", type text},

            {"DB_Name", type text}, {"Schema_Name", type text}

        }

    )

in

    _DataType

Step 2: Define a Query Parameter for Dynamic Environment Selection

We need to define a list parameter as p_Query_Environ that controls which environment (Dev, Test, or Prod) Power BI should use when running queries. 

This parameter acts as a switch, allowing you to easily toggle between environments without editing the query code.

Step 3: Pass Connection Strings Dynamically to Queries

Instead of hard‑coding server, database, and schema names, we will filter the Environment Variables Table using the parameter (p_Query_Environ) and feed those values directly into the query connection. 

This makes our queries flexible and environment‑aware, ensuring that Power BI automatically connects to the right source based on the environment we select.

Example: dim_Country

let

    /* 

    Filter the Info_Query_Environment table based on Parameter Value (Eg. Dev, Test, Prod)

    */

    SelectedEnv = 

        Table.SelectRows(Info_Query_Environment, each [Source_Env] = p_Query_Environ){0},

    /* Assign the values to Environment Variables */

    _ServerName   = SelectedEnv[Server_Name],

    _DatabaseName = SelectedEnv[DB_Name],

    _SchemaName   = SelectedEnv[Schema_Name],

    /* SQL Query with dynamic Environment Variables */

    vSrc_Qry = "Select Distinct [Country_ID], [Country_Name], [Currency_Code]

         From " & _SchemaName & ".dim_Country",

    /* Run the SQL Query */

    Source = Sql.Database(_ServerName, _DatabaseName, [Query = vSrc_Qry])

in

    Source

Final Step (Optional): Save Report as Power BI Template
We can save your report as a Power BI Template (.PBIT), which captures the report structure, queries, and parameters, but leaves out the actual data. This makes it ideal for scenarios where different users need to run the same report against different environments (Dev, Test, Prod).

Provide the description to the Report Template while saving.

When a user opens the Power BI Template, it automatically prompts to choose a value for the p_Query_Environ parameter. The options (e.g., Dev, Test, Prod) come directly from the parameter definition that we created earlier.

Based on the selection, Power Query filters the environment table and connects to the correct server, database, and schema for that environment. 

Once the environment is chosen, the user simply clicks Load to run the report, and Power BI retrieves data from the selected environment seamlessly.

Note:
You can refine this method to fit your own scenario. Adjust the setup as needed for your environment.

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

Thanks

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