Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
Hi everyone,
I am not able to connect a direct query from sql to power bi
it shows 'this query contains transformations that can't be used for a live connection'
Can you please help. Basic query like select * , I am able to run not a big query contains many tables in it. I tried the query in SQL server , it works and results came too.
Thanks in advance.
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
Hi Angelia,
Thanks a lot for your response appreciate it a lot. I will re-create my query.
Hi,
Did you check the query in SSMS?
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.
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
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
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
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
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...
P
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
I'm getting this too. Can't we call sql funtions in DirectQuery mode?
We've just hit this issue in one of our databases. Its accessing a TABLE.
The table is using user defined data types!
A copy of the table using the underlying SQL SERVER types wokrs fine. The user has data_reader and public roles/permisisons
To give them appropriate access you need to give then VIEW DEFINITION permissions so the DB can resolve the undelrying schema shenanigins into some normal looking datatypes that PowerQuery can get its head around
GRANT VIEW DEFINITION TO TestUser;m
if you have lots of users or are using groups/roles you can grant VIEW DEFINITION to the public role of the DB assuming you're happy with the security implications.
GRANT VIEW Definition TO PUBLIC;
This seemed to work for me/us
I am also faceing same error. but i run my query in SSMS its succeffully run.
i am checking once again my select statement 2 line that way error
below sql statemement error becoz select statemenet 2 line that error in power bi
select col1,col2,col3,clo4
,col4,col5,col6 from TABLE1 T1
INNER JOIN TABLE2 T1 ON T1.A=T2.A
after change my query its succefully loadede in Power BI(becoz my Sql select statement is 1 line)
select col1,col2,col3,clo4,col4,col5,col6 from TABLE1 T1
INNER JOIN TABLE2 T1 ON T1.A=T2.A
any body facing this type error check once again ur query
Thanks
Aruna
Having similiar issue. When I call a view from a function, I get the 'transformation' error. When I call the view directly I get zero issues. When I call the function with the query from the view in it (Table valued function) I get zero problems. Not doing any transformations so this seems to be a misleading error. Anyway, my work around is embed the view query in the function.
( Ihave to report to a log who viewed what when, the TVF is my only route to use PowerBi given the security requirements. and it has to be direct query until I can refresh imported data more than once a day.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |