cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
arobin Frequent Visitor
Frequent Visitor

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 contains transformations that cannot be used for live connection" means? I was trying to get a live data in SQL Server using SQL statement below.

 

select a.PAYMENT_TYPE, count(a.PAYMENT_TYPE)
from VENDOR_ACCOUNTS a,
(select * from VENDOR_Keys where RemoteSystem='ECC_VENDOR') b
where a.InternalId=b.InternalId
GROUP BY a.PAYMENT_TYPE
order by a.PAYMENT_TYPE

 

when I  cliked on load data the said mesage appeared.

is there something wrong in my query that affects the live connection?

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

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

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 

17 REPLIES 17
Thiyagu Member
Member

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

Hi,

 

Did you check the query in SSMS?

v-huizhn-msft Super Contributor
Super Contributor

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

Hi @arobin,

It should be error in the SQL statement. You'd better join the two table together, and "where a.InternalId=b.InternalId" is improper. You'd better propram the SQL like the following syntax.

select a.*from Table a

inner join

(select * from Table2) b

on  a.InternalId=b.InternalId

GROUP BY a.PAYMENT_TYPE
order by a.PAYMENT_TYPE

And as the @Thiyagu, please test it successfully, then use it to import the data.

Best Regards,
Angelia

arobin Frequent Visitor
Frequent Visitor

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

Hi Thiyagu,

 

I don't have SSMS installed yet. Trying to get the installer. I am using Oracle SQL Developer to test the query and that test query runs fine there. I wonder if that kind of query runs fine in SSMS. Thank a lot for your response Thiyagu. It helped me a lot as a newbie here.

arobin Frequent Visitor
Frequent Visitor

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

Hi Angelia,

 

Thanks a lot for your response appreciate it a lot. I will re-create my query.

 

 

v-huizhn-msft Super Contributor
Super Contributor

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

Hi @arobin

Please feel free to ask if you have other problem.

Thanks,
Angelia

v-huizhn-msft Super Contributor
Super Contributor

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

Hi @arobin,

If the solution help your resolve your problem, please mark the corresponding reply as answer, so that other people can find solution easily.

Best Regards,
Angelia

arobin Frequent Visitor
Frequent Visitor

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

Hi Angelia,

 

Sorry for this late response.

 

I changed the code but still got the same error.

 

Select VENDOR.MDM_PAYMENT_TYPE, count(VENDOR.MDM_PAYMENT_TYPE)
FROM MDM_TRANSACTIONAL_VENDOR_ACCOUNTS_27 VENDOR
LEFT OUTER JOIN
  (Select * from MDM_TRANSACTIONAL_VENDOR_ACCOUNTS_27_Keys where RemoteSystem='SAP_ECC_VENDOR') VENDOR_KEY
        on(VENDOR.MDMInternalId=VENDOR_KEY.MDMInternalId)
Group by VENDOR.MDM_PAYMENT_TYPE
Order by VENDOR.MDM_PAYMENT_TYPE

 

 

v-huizhn-msft Super Contributor
Super Contributor

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

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 Frequent Visitor
Frequent Visitor

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

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