How to change the Data source connection from 'SQL Server database' to 'Azure SQL Server database'

[ta5656]

Hi,

I need a help regarding changing of data source connection from SQL Server database to Azure SQL Server database. Litte background, I have created a Power BI report using the 'SQL Server database connection' but I want to change it to the 'Azure SQL Server database', because I have same tables on Azure. So, is there a way we can do this on the PowerBI desktop?

I want to change to this 'Source' connection

Thanks!!

[SNawalKishore1]

Hello @ta5656 ,

I hope this will help you.

Step 1. Create new AzureDB.PBIX with Azure SQL DB Connection.

Step 2. Open SQLDB.PBIX with On-Prem SQL Server DB Connection, then go to Edit Query > Advance Editor and copy all Applied Steps "Except" Source (which is on-prem sql server).

Step 3. Go to AzureDB.PBIX, then go to Edit Query > Advance Editor paste all steps "Except" Source(which is New Azure SQL DB).

Step 4. Repeat Step 2 & Step 3 until all tables replaced with Applied steps.

Step 5. Then switch back to old PBIX, copy the all visuals from SQLDB.PBIX and Paste to AzureDB.PBIX as all the Tables & Column Names are identical.

Please try and let me know.

Cheers,

Nawal
+91-9866755720 | Power BI Consultant

https://www.linkedin.com/in/snawalkishore/

[ta5656]

Nawal, I appreciate your continues help on this issue. As per you steps below, It's working partially that means I able to copy the visuals from one report to another report - which is really a good thing, but I have to create the 'Measures' once again on the Azure connection report. 

Thanks!!

[SNawalKishore1]

Hello @ta5656 ,

Very Simple.

Open Power BI Desktop > Open Edit query > Left side in Queries Pane> any table > Right side in Applied Steps > Double click on Source > Change Server name and database name and Click OK.

Cheers,

Nawal
+91-9866755720 | Power BI Consultant

https://www.linkedin.com/in/snawalkishore/

[ta5656]

Thank you! Kishore, for your reply. I did that, but as per my testing, the way the connectors work seems like different because when you select 'SQL Server database' as your source at the first step and you deploy the report to service you can schedule the 'data set' through the gateway. But, if you select the 'Azure SQL Server' database at the first step by default it will automatically refresh every 1 hour on the Power BI service because I selected the 'Azure SQL Server' as my connector at the very first step. So, if you change the database name it will work but it won't bring the properties of the 'Azure SQL Server' like 'Cache' dataset refresh, I'm looking for changing the 'SQL Server database' to 'Azure SQL Server' at the very first step.

Thanks!!