cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsBrizius
Helper I
Helper I

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
WESTi
Helper I
Helper I

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.

View solution in original post

21 REPLIES 21
ChGe
Frequent Visitor

Its Aug 2020 and this is still an issue. I can't belive such a basic feature doesn't work...

RichardBroadley
New Member

This is still an issue. CTE's in Direct Query mode work in Preview but errors when you try and use it. As far as I can see, the 'solution' is that it's been raised with Microsoft. Is there an update? Are Microsoft going to fix this?

Still an issue in April 2020 version. Hopefully they will fix this issue soon.  I do appreciate the workarounds in the thread.  If you have permissions to to create a view, you can copy your CTE in the SELECT/FROM block of the view, as someone mentioned above. 

Still an issue in May 2020. Wonder if this was brought up internally. It's been almost four years from first post.

andrewt
Advocate III
Advocate III

Why is this issue marked "Solved"?  Power BI still throws an error when closing the Query Editor for any query containing a CTE (which seems pretty insane given the amount of time this has been a known issue). 

Anonymous
Not applicable

Confirming this is still an issue 5 months from last post for anyone coming across this thread!

Agreed. The workaround given earlier in this thread creates a table that is in import mode, so no improvement on using import mode in the first place that I can see.

EVEAdmin
Helper V
Helper V

So, it seems CTEs are only supported in Import mode and not in Direct Query. Still the case?

Anonymous
Not applicable

To be fair I have not tested recently. But I have not seen an update on the matter from Microsoft. I will test again when I have some time and post and update here.

TravisFollett
Regular Visitor

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. 

 

Anonymous
Not applicable

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

bdmlc
Frequent Visitor

You can work around it with a sub-query (just move the CTE in to your main query as a sub-query), which is crude and very SQL2K'ish but that is the world we live in, apparently.

WESTi
Helper I
Helper I

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.

View solution in original post

Creating views instead of CTE is not always an option.

 

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

 

 

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

v-qiuyu-msft
Community Support
Community Support

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.

@v-qiuyu-msft  Was there ever a response/feedback internally on this?

Anonymous
Not applicable

I don't think offically. I was able to work around it with making the query into a view and then selecting from there.

Is there any fix to this issue till date?

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors