Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

28 REPLIES 28
wallace13
Advocate II
Advocate II

Still an issue in March 2024.  This is a bug and needs to be fixed.  Using import is not a work around for a lot of applications.

My current workaround is using subqueries, but of course gets a bit ugly structure-wise... @Microsoft: please add CTEs to directquery - what's the problem with solving this "bug"?

.... just encountered this issue in the middle of the night last night trying to get a dashy board ready for today,  grateful for this community as at least i was able to pop up a quik sql view to get it working

Beerens
New Member

It is still an issue (nov 2023)

JChai
Frequent Visitor

On September 2023, this is still an issue: Microsoft SQL: Incorrect syntax near ';'. Incorrect syntax near ')'. 

PatrickWang
New Member

I got the same error, the temporary solution is to select 'Import' instead of 'Direct Query'. It did the trick for me.

UnbendingNose
Regular Visitor

Confirmed this still is a bug and CTE's don't work in direct query mode. This is sad.
ChGe
Frequent Visitor

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

RichardBroadley
Regular Visitor

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. 

Anonymous
Not applicable

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

Anonymous
Not applicable

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors