cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jsBrizius Regular Visitor
Regular Visitor

Custom query with CTE not supported (bug?)

Is creating a custom query using a CTE officially not supported or am I just duing it wrong?  It works in Query Editor, but you cannot close and apply.  I suspect it is becuse the query seems to be embeded into another select when you try to run the close and apply.

1 ACCEPTED SOLUTION

Accepted Solutions
WESTi Regular Visitor
Regular Visitor

Re: Custom query with CTE not supported (bug?)

I ran into this issue when attempting to use Direct Query Mode. As Qiuyun has said, you can use import mode as a way around this but then you obviously have the timing issues with the data set being dependent upon being refreshed (not live).

 

I ended up having to build most of my data sets in SQL server using views etc and then direct connect to those.

15 REPLIES 15
Moderator v-qiuyu-msft
Moderator

Re: Custom query with CTE not supported (bug?)

Hi @jsBrizius,

 

I have reproduced the issue in DirectMode. When use With CTE clause, Power BI Desktop will treat this clause as a derived table, then cause syntax error. We can use SQL Profiler to trace executed command. See:

 

z1.PNG

 

a2.PNGa3.PNGa4.PNG

 

I will report it internally and will give you update once I get any feedback. Currently, you can use Import mode instead of DirectQuery mode to workaround the issue temporarily.

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
WESTi Regular Visitor
Regular Visitor

Re: Custom query with CTE not supported (bug?)

I ran into this issue when attempting to use Direct Query Mode. As Qiuyun has said, you can use import mode as a way around this but then you obviously have the timing issues with the data set being dependent upon being refreshed (not live).

 

I ended up having to build most of my data sets in SQL server using views etc and then direct connect to those.

Vlasz Frequent Visitor
Frequent Visitor

Re: Custom query with CTE not supported (bug?)

Just wanted to bump this topic.

 

Please fix this!!!

 

I use CTE's quite often in my SQL, and re-writing my queries so Power BI will "like" them both adds another step and throws a wrench into my query formatting.

 

Not to mention how frustrating it is to see your query evaluate, get a preview of your data, and then on the second query evaluation be told that incorrect syntax has been detected.

 

Thank you

Mandr0id Frequent Visitor
Frequent Visitor

Re: Custom query with CTE not supported (bug?)

Creating views instead of CTE is not always an option.

 

Need either CTE ot Temp tables support in PowerBI when creating data sets.

 

 

preese Occasional Visitor
Occasional Visitor

Re: Custom query with CTE not supported (bug?)

Any update on this issue? Still a problem over a year and a half later.

adrez86 Regular Visitor
Regular Visitor

Re: Custom query with CTE not supported (bug?)

What happened with this? Would like an update from over a year and half ago

Re: Custom query with CTE not supported (bug?)

Is there any fix to this issue till date?

Re: Custom query with CTE not supported (bug?)

First post here -

 

It is possible to use CTE's for direct query sources in Power BI.  The issue, as was pointed out by @v-qiuyu-msft, is that the dataset is treated as a derived table and is illegal in the context of direct query (boo).

 

To get around this, I did the following (this feels like a hack at first, but it does work):

 

1) Create direct query dataset with a CTE - let it error, then hit Edit Queries.

2) COPY/PASTE the dataset with the CTE, and Edit the query in the new one so that it selects out the same columns as the CTE result set, only '' AS [Column1], '' AS [Column2], etc...

3) Append the two queries together as a NEW dataset, then right-click and disable 'Enable load' for both of the sources, so only the new, combined datasest is pushed up.

 

I realize this is a few more steps than just writing a CTE and having it work, but at least it does.  I have a few queries with a not-so-wide result set that would take a lot of time to rewrite, not to mention the embedded logic would slow them way down.  For those, this is great.

 

To be clear, this is an example source query that throws an error:

;

WITH dataset (COLUMN1, COLUMN2, COLUMN3)

AS (SELECT 'TEST1',

           'TEST2',

           'TEST3'

   )

SELECT dataset.COLUMN1,

       dataset.COLUMN2,

       dataset.COLUMN3

FROM dataset

 

Copy/Paste that and then edit the 2nd one to this:

SELECT '' AS COLUMN1,

              '' AS COLUMN2,

       '' AS COLUMN3

 

Combine the above, and disable their load param, only load the 3rd resulting set to your report. 

 

bjcw0 Frequent Visitor
Frequent Visitor

Re: Custom query with CTE not supported (bug?)

Does anyone know if this has been solved?

 

I saw the work around, but it would be much easier if it worked at a appilcation level.

 

Thanks!

 

B

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 95 members 1,515 guests
Please welcome our newest community members: