This is a handy trick for switching between the SQL Server instances and databases that a Power BI file is connected to & is particularly useful if, like myself, you develop and/or test against one environment & then want to connect the final Power BI file to the production database.
Once the Power BI development is complete & it’s time to switch to the production database (or back to development/test for redevelopment & bug fixing), follow the below steps. Bear in mind that the schema must be the same on the development/test & production environments for the views &/or tables referenced by your Power BI file, & you must have appropriate permissions on both environments.
- In the Power BI Desktop Home menu, click Transform Data to open the Power Query Editor.
2. In the Power Query Home menu, click Data Source Settings to open a pop-up window.
3. Choose “Data sources in current file”, highlight the relevant data source & click Change Source.
4. In the SQL Server Database pop-up window, update the connection details by typing in the Server & Database fields. Click Ok.
5. In the Data Source Settings pop-up window, you’ll see that the data source name has been updated. Click Close.
6. In the Power Query Editor Home menu, click Refresh All to refresh the preview.
7. Click Close & Apply to apply the change to the Power BI file. The query changes will apply automatically.
…
Be First to Comment