Scenario:
Step 2: Define a Query Parameter for Dynamic Environment Selection
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
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).
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.
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
--------------------------------------------------------------------------------------------------------
