Forum Discussion

Sham_24's avatar
Sham_24
Frequent Visitor
2 years ago
Solved

changing sql server connection for Direct Query

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?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Sham_24
      Frequent Visitor
      2 years ago

      Thanks YongKang Hua. It worked beautifully. 

  • Sham_24's avatar
    Sham_24
    Frequent Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.