In this blog, I will show you how you can switch between databases instantly in Power BI Desktop as well as Power BI service. By following this method, you will skip the hassle of downloading the PBIX files each time a source change is needed.
One thing we need to consider here is that the schema of the databases must be the same. The tables originally used to create the report should be present in the new database as well, the data can be different.
A very common use case is if you want to often switch data between development & production environments in the same report file.
Blog structure:
- Connect SQL server to Power BI
- Create Parameters
- Make the ‘source string’ dynamic
- Switch between databases in Power BI desktop as well as Service
Connect SQL server to Power BI
Step 1: Either select ‘SQL Server’ in Home ribbon or click on ‘Get data’ & select ‘SQL Server’.
Step 2: In SQL Server database dialog box, enter the Server name & Database (optional). Default data connectivity mode will be ‘Import’, you can also select ‘DirectQuery’ according to your report needs.
Step 3: Enter your credentials
Step 4: In the ‘Navigator’ window, select the tables & click on ‘Transform Data’. Clicking on Transform data will take you to the Power Query Editor.
Create Parameters:
Step 1: In Power Query Editor, from Home ribbon click on Manage Parameters->New Parameter
Step 2: Fill in the details in the Manage Parameters Dialog box. Give a name & then change the suggested values to ‘List of values’ & enter strings of both Development & Production databases. Select the default & current value of your choice. Click on Ok.
In my case I’ve created 2 parameters i.e. ‘Server Name’ & ‘Database Name’
Make the source strings dynamic:
Step 1: In Query editor, select one of the tables & then select ‘Advanced Editor’ from the Home ribbon. The M script will look something like this:
Step 2: In ‘Source’, replace the actual server & database names with the name of the Parameters that was created in the earlier step.
Here’s how the script will look after you’ve made the changes:
Step 3: Click on Done. Close & apply the Query editor.
Switch between databases in Power BI desktop as well as Power BI service:
- In Power BI Desktop, in the Home ribbon, click on the drop down for ‘Transform Data’ & select ‘Edit Parameters’. A dialog box will appear like the image below.
Select the required server & database name and click on Ok. This will change the actual data source of your PBIX file. (It will ask you to enter the credential when you do it for the first time.)
- In Power BI service, after publishing the report, go to the Dataset Settings & then the ‘Parameters’ section. Here you will find text boxes for entering your new connection strings. Unlike PBI desktop, you will not get a drop down here, but you can still enter the connection strings manually.
Karan Nair
Team Lead – Data Analytics
Addend Analytics