Change Database Server Directly from Power BI Service (No PBIX Download Needed!)

[anmolmalviya05]

Switching between development and production databases in Power BI can often be a hassle — especially if you're manually editing PBIX files every time. But what if you could dynamically change the database connection directly from the Power BI Service?

In this blog, I’ll show you exactly how to make your SQL Server connection dynamic using parameters, allowing seamless switching between databases without downloading your PBIX file.

💡 When Is This Useful?

This approach is ideal when:

• You need to toggle between Dev, UAT, and Production environments
• You want to manage data source switching in the Power BI Service
• Your databases share the same schema (only the data differs)

📝 Blog Overview:

• Connect SQL Server to Power BI
• Create Parameters
• Make the Source String Dynamic
• Switch Databases in Power BI Desktop & Service

1️⃣ Connect SQL Server to Power BI

Step 1:

In Power BI Desktop, go to Home > Get Data > SQL Server.

Step 2:

In the connection dialog:

• Enter the Server Name
• (Optional) Enter Database Name
• Choose your preferred Data Connectivity Mode (Import or DirectQuery)
  

Step 3:

Enter your SQL credentials.

Step 4:

In the Navigator pane, select the required tables and click Transform Data to enter Power Query Editor.

2️⃣ Create Parameters

In Power Query Editor:

Step 1:

Go to Manage Parameters > New Parameter.

Step 2:

Fill out the details:

• Set the parameter name (e.g., ServerName, DatabaseName)
• Change Suggested Values to List of values
• Add both Dev & Prod strings
• Set your desired default and current values
  

🛠️ Example:

• ServerName → dev-server, prod-server
• DatabaseName → SalesDev, SalesProd

3️⃣ Make the Source String Dynamic

Step 1:

In Power Query Editor, select any table and open Advanced Editor from the Home ribbon.

Step 2:

You’ll see the M code. Locate the part that defines the source, like:

Source = Sql.Database("dev-server", "SalesDev")

Replace the hardcoded values with your parameters:

Source = Sql.Database(ServerName, DatabaseName)

Click Done, then Close & Apply.

4️⃣ Switching Databases in Power BI Desktop & Service

🔄 In Power BI Desktop:

• Go to Home > Transform Data > Edit Parameters
• Select your target server and database
• Click OK — and credentials will be requested (first time only)
  

🌐 In Power BI Service:

• After publishing your report, go to Dataset Settings > Parameters
• You’ll see text boxes to enter new values for your parameters
• Update the values and refresh the dataset

⚠️ Note: In Power BI Service, dropdowns won’t appear — but manual entry works just fine.

✅ Final Thoughts

This method is a life-saver for developers and admins, making it easy to:

• Maintain a single PBIX file
• Manage environments directly from Power BI Service
• Reduce the time spent on source modifications
• If you're working in a multi-environment setup, this is a must-have technique in your Power BI toolbox!
  

📢 Let’s Connect!

Best regards

Anmol Malviya

Sr. Data Analyst | Addend Analytics