changing sql server connection for Direct Query

[Sham_24]

I am new to Power BI. I am trying to change my SQL server data source connection to different SQL server data source connection in Direct Query mode. It is asking me to change it to import mode to do this. How can I change the sql server connection in direct query mode?

[Sham_24]

Thanks Yongkang Hua for your reply. I followed your steps. But when I go to Tranform Data and go to the Applied Steps-> Source. I see error below.

[Anonymous]

Hi, @Sham_24 

You can create three parameters in Power Query, representing the SQL Server data source, database, and data table, and then modify them in the Advance editor, as shown below:

Create three Parameters

This is my sql server

I'm in the M language in the Advanced Editor and my table data in sql server

let
    Source = Sql.Databases(source),
    SQLTestDB = Source{[Name=basename]}[Data],
    dbo_MyTable = SQLTestDB{[Schema="dbo",Item=tablename]}[Data]
in
    dbo_MyTable

Next, I'm going to make changes to the data source from VXINRU64VMTEST_ZXR to VXINRU64VM data source, and make changes to the database and data tables, you need to do it in the parameters.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi, @Sham_24 

To change a SQL Server data source in DirectQuery mode without switching to import mode, in the Data Source Settings window, locate the current SQL Server data source, and then select Change Feed.

Enter the details of the new SQL Server you want to connect to, including the server name and database. Confirm the changes and close the Data Source Settings window.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sham_24]

Thanks YongKang Hua. It worked beautifully.