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.
