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 (15)
RE: Custom cte Query in Direct Query Power Bi
It seems insane that this feature (still) does not exist. Like many here, spent hours writing a series of complex queries using CTEs and then they don't work...
My frustrations using Power BI are mounting, and as much as I hate Tableau I'd rather be using that at this point. Considering advising my Azure client to stop paying for these licenses, and to use another product. So frustrating.
RE: Custom cte Query in Direct Query Power Bi
Please, please, please start supporting cte's. We should not be having to grant privileges to write views when cte's can do the trick. I know the solution should be 'close' as we can see our data perfectly in Power Query! I just want to be able to successfully 'close and apply', then use my data!
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