Custom cte Query in Direct Query Power Bi

[Srividya]

In Power Bi a custom cte query in DirectQuery is giving issues.This feature has to be enabled.As a workaround we can use import mode but when live data is required directquery has to be used.Right now this is not supported.

[james_veitch]

Please fix this, such a pain

[rafael31]

Please give us a solution to this issue, we need cte in ours querys.

[caroleh]

To Power BI developers: The ability to write a query using CTEs is an absolute must. We are looking at adding Power BI to our menu to roll out to our customer base with access to pre-defined templates. We need the ability to write proper queries. Please add this to your bucket list to address. Thank you.

[clifford_dibble]

I was under the impression that "Power Query is the same in Excel and Power BI". Now I find that the corpus of CTEs from the Excel PQ world is not usable in Direct Query mode of PBI. This is a very frustrating user experience that violates the principle of least astonishment. Please fix.

[joyce_armajo]

I have a number of queries that use CTEs that run just fine in sql server. then imagine the frustration and time lost (that's the major issue) in trying to get Power BI to accept the query (direct connect), then realizing you've got to basically rewrite the code (if you can) with embedded subqueries (this is very difficult to do and virtually impossible to follow once you've done it). I cannot create views. This is something that users need and expect. PLEASE fix this. Thanks

[castruita01]

my company has decided to pass on tableau and use powerBi instead, i feel like this is such a huge mistake as i've never had issues with even temp tables in tableau, and now this is just silly that i have to create a view for each dashboard for a direct connection..even if its just a simple query???..... wow what a step backwards.

[sql_guy]

E X A C T L Y ! Data analysis and reporting (as opposed to simple CRUD operations) are when CTEs and temp tables come in most handy. Power BI, which exists for the purpose of analysis & reporting, isn't much useful if it only supports simple CRUD operations.

[fbcideas_migusr]

Yes, we can build views as a work around, but many of the report writers do not have permissions to create a view. Just read access. We're training staff to use CTE's as it provides a great way to isolate complex query logic. Much cleaner than using sub-queries. From the perspective of self-service, lack of support for CTE's is limiting. We have an on-premises version ( PBIRS) so direct query is superior for real-time data access.

[fbcideas_migusr]

I just ran into this issue. You can get a review in Power Query but once you Close & Apply you get errors about semicolons.

[dave40]

Wow I was just writing a big cte and figured I better check if I was going to be able to use it, back to the drawing board...