Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Direct Query - Queries using CTE's are not being accepted

Hi, I have a Query that runs T-SQL code with 3 CTEs as part of the code.  It's in import mode and works great with no issues  But when creating a new direct query and using this code, I get the fol...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    I'm afraid the answer is No. As of now, CTEs still don’t work in DirectQuery mode. I find the following description from official document Data transformation limitations of DirectQuery. 

     

    Also, the Get Data dialog or Power Query Editor use subselects within the queries they generate and send to retrieve data for a visual. Queries defined in Power Query Editor must be valid within this context. In particular, it's not possible to use a query with common table expressions, nor one that invokes stored procedures.

     

    As a workaround, consider building your data sets in SQL Server using views and then connecting directly to those views in Power BI. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks very much for the feedback and the specifics. I guess an answer, even if it isn't the one I was hoping for, is better than not knowing.
    I'll make changes to the queries with the CTE's.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous 

 

I'm afraid the answer is No. As of now, CTEs still don’t work in DirectQuery mode. I find the following description from official document Data transformation limitations of DirectQuery. 

 

Also, the Get Data dialog or Power Query Editor use subselects within the queries they generate and send to retrieve data for a visual. Queries defined in Power Query Editor must be valid within this context. In particular, it's not possible to use a query with common table expressions, nor one that invokes stored procedures.

 

As a workaround, consider building your data sets in SQL Server using views and then connecting directly to those views in Power BI. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

zbWorkProfile's avatar
zbWorkProfile
Frequent Visitor
2 years ago

Do we know if that's slated to change anytime soon? A big reason to use CTE is needing to create calculated columns which would then be referenced in the actual query; converting CTE to subqueries blows up quickly in that scenario. Needing to push the build back to a SQL Server view just to be able to DirectQuery that data is both (1) unnecessary extra steps, and (2) extraneous and unnecessary internal resource diversion.