Forum Discussion

arobin's avatar
arobin
Frequent Visitor
9 years ago
Solved

Create Connection: This query contains transformations that cannot be used for live connection

 I am very new to Power BI and I am not familiar with the message I encountered when trying to make a DirectQuery connection to SQL Server. Can anyone advise what does "Create Connection: This query ...
General Comment
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi arobin,

     

    Could you please test using a simple SQL statement? You can create a simple table named TableA in your database, then type the basic query statement using like: select * from TableA in Direct Query model, check if it works. Some function is not used in Direct Query model. If it works fine, it will document the function is unable. You'd better test step by step.

     

    If the function is not used, please create a new result table, then import them to Power BI. If you have any issue, please let me know.

     

    Best Regards,
    Angelia 

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi arobin,

First, you should debug your SQL statement to work fine in SSMS. The code run correctly in SSMS? Then you type it in Power BI. If you have any issue about T-SQL, please post your issue to Transact SQL forum to get professinal support.

Best Regards,
Angelia

arobin's avatar
arobin
Frequent Visitor
9 years ago

Hi Angelia,

 

yes it worked in SSMS and I copy paste the query in power BI and still got same issue. 

Thanks for the advise, I will try on the T-SQL forum.

 

I apprreciate all your help and your time...

 

Andre

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi arobin,

    If it works fine in SSMS, it should be fine in Power BI. Please verify you connect the SQL Server database successfuly.

     

    Best Regards,
    Angelia

    • arobin's avatar
      arobin
      Frequent Visitor
      9 years ago

      Hi Angelia,

       

      The query actually worked when I choose "Import" connectivity mode and the result gets loaded well.

      However, if I use "DirectQuery" connectivity mode, it only return the result of the query and when I hit "Load"

      it fails...

      Please see below...

       

      PBut when I go and hit "Load" this message appears.

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi arobin,

         

        Could you please test using a simple SQL statement? You can create a simple table named TableA in your database, then type the basic query statement using like: select * from TableA in Direct Query model, check if it works. Some function is not used in Direct Query model. If it works fine, it will document the function is unable. You'd better test step by step.

         

        If the function is not used, please create a new result table, then import them to Power BI. If you have any issue, please let me know.

         

        Best Regards,
        Angelia