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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

27 REPLIES 27
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.

.... 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
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. 

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.

Creating views instead of CTE is not always an option.

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.