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

[PrakashGupta]

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.

[v-qiuyu-msft · Accepted solution]

@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

[v-qiuyu-msft]

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

[hugoberry]

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

[v-qiuyu-msft]

@hugoberry Thanks for your prompt.

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

Best Regards,
Qiuyun Yu

[hugoberry]

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

[hugoberry]

Have you tried doing the date conversion in Power Query or data model instead?

[Greg_Deckler]

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