cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
v-huizhn-msft Super Contributor
Super Contributor

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

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

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

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...

 

PSQL result.JPGCreate Connections error.JPGCreate Connections error.JPGBut when I go and hit "Load" this message appears.

 

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 

Anonymous
Not applicable

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

I'm getting this too.  Can't we call sql funtions in DirectQuery mode?

stpnet Established Member
Established Member

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

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

Aruna_ln Member
Member

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

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

JackSprat Regular Visitor
Regular Visitor

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

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.

hiralsoni_001 Frequent Visitor
Frequent Visitor

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

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.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 57 members 1,124 guests
Please welcome our newest community members: