Reply
Occasional Visitor
Posts: 1
Registered: ‎04-12-2017
Accepted Solution

CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date in a

Hi All,
I am getting an error "This query contains transaformation that cannot be used for a live connection". I am using CONVERT(VARCHAR(10), Date, 20) in my query and using DirectQuery option instead of import.

 

Also if you can add some link how to use query in porwer BI that will be useful.

 

Thanks in advance.

 


Accepted Solutions
Highlighted
Moderator
Posts: 3,019
Registered: ‎03-06-2016

Re: CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date i

@PrakashGupta@hugoberry I got the information that DirectQuery works is by taking the user query and putting it into a subselect. In order for this to work, it needs to be able to reference columns in the user query by name. Duplicate names or names that are missing would prevent that from working. So this is effectively by design.

 

In your scenario, please specify a alias for that convert() results in T-SQL query as suggested in my first post.

 

Best Regards,
Qiuyun Yu

View solution in original post


All Replies
Super Contributor
Posts: 4,409
Registered: ‎07-11-2015

Re: CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date i

When you say in your query, you are referring to SQL, correct?

Member
Posts: 99
Registered: ‎02-03-2017

Re: CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date i

Have you tried doing the date conversion in Power Query or data model instead?
Member
Posts: 99
Registered: ‎02-03-2017

Re: CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date i

Just tested your query. Indeed you get the problem if you don't specify a name for the column. Give an alias to convert... call either with AS or with [date_converted]=convert ... construct
Moderator
Posts: 3,019
Registered: ‎03-06-2016

Re: CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date i

Hi @PrakashGupta,

 

Based on my tested in desktop version 2.45.4704.442 with DirectQuery mode, the T-SQL query below can works fine.

 

select CONVERT(VARCHAR(10), DateCol, 20) Col1
from [dbo].[DateVal]

 

In your scenario, I guess the issue my not related to Convert() function itself. The error message indicates there might have some action is not supported in DirectQuery mode. Please review the Limitations of DirectQuery . 

 

Would you please share the T-SQL query you used in desktop? Except this query, is there any other queries or transform actions performed in desktop?

 

Best Regards,
Qiuyun Yu

Member
Posts: 99
Registered: ‎02-03-2017

Re: CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date i

As mentioned in my reply, you can replicate the issue if you don't give an alias to CONVERT call

Moderator
Posts: 3,019
Registered: ‎03-06-2016

Re: CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date i

@hugoberry Thanks for your prompt. Smiley Happy

 

@PrakashGupta I already sent a email to consult this issue internally, will update to you if I get any feedback.

 

Best Regards,
Qiuyun Yu

Highlighted
Moderator
Posts: 3,019
Registered: ‎03-06-2016

Re: CONVERT(VARCHAR(10), Date, 20) is creating issues in direct query option while converting date i

@PrakashGupta@hugoberry I got the information that DirectQuery works is by taking the user query and putting it into a subselect. In order for this to work, it needs to be able to reference columns in the user query by name. Duplicate names or names that are missing would prevent that from working. So this is effectively by design.

 

In your scenario, please specify a alias for that convert() results in T-SQL query as suggested in my first post.

 

Best Regards,
Qiuyun Yu