Forum Discussion
Query with CTE gives error
Are you accessing a view that uses a CTE or did you write a SQL query that uses a CTE?
If it's the latter, then I'd suggest creating a view and then querying the view.
I wrote a query that uses a CTE. I don't have permissions to create a view or stored procedure.
Gotcha, putting a custom sql query into the query builder is not recommended - as you can see :smileyhappy:
Here are your options ...
1. Create a view - sounds like this isn't an option for you
2. Re-write the query without using a CTE - that would be a good test of your SQL skills
3. Stop using the custom sql code, query the underlying tables using the GUI, and use PQ to create the output you want - that would be a good test of your PQ skills
I am not sure why it isn't recommended. I don't see the documentation saying that and I don't see a list of query limitiations.
I am evaluating Power BI to determine in my organization wants to buy and use it as a visualization tool. I am not trying to "test my skills". I am trying to evaluate products to see how they work (or if they work in this case).
I cannot import the tables and join them because they are too large. I cannot use Direct Query because it doesn't support many to many relationships. All I see are a lot of llmitations.
Power BI is supposed to be self-service. That typically means not writing views or stored procedures (and generally not CTEs) and if a feature is there, I expect it to work.
your issue and thoughts about it are exactly what I'm facing today. Would be nice if it jsut passed through the query and used the results like it does in the preview. My query also works fine in PowerPivot with a recursive CTE and I was just trying it in Power BI but the same code in Power BI isn't accepted. cheers
