on 21 Jul 2017 11:25:10
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.
- Comments (13)
RE: Custom cte Query in Direct Query Power Bi
Please implement a solution as this has been a known issue/request for over 4 years.
RE: Custom cte Query in Direct Query Power Bi
Just spent half a day wriing a complex query usign CTEs in power query (excel) and then come to power BI to implement...
RE: Custom cte Query in Direct Query Power Bi
I feel this issue has already existed for more than 2 years. When will Microsoft take action??
RE: Custom cte Query in Direct Query Power Bi
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...
RE: Custom cte Query in Direct Query Power Bi
I just ran into this issue. You can get a review in Power Query but once you Close & Apply you get errors about semicolons.
RE: Custom cte Query in Direct Query Power Bi
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.
RE: Custom cte Query in Direct Query Power Bi
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.
RE: Custom cte Query in Direct Query Power Bi
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
RE: Custom cte Query in Direct Query Power Bi
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.
RE: Custom cte Query in Direct Query Power Bi
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.