At the time of writing, it is not possible to write a query using a CTE in the source of a dataflow. However, there are a few options to deal with this limitation:
- re-write the query using subqueries instead of CTEs
- use a stored procedure that contains the query and reference the stored proc in the source of the dataflow
- write the query as a view and reference the view in the source of the dataflow (this is my preferred method and the one I will demo here)
I will use the following query purely as an example to demo this:
This query produces the following output:
If I write this query directly inside the source of the data flow as shown below, when trying to import the schema or preview the data I get the error message Incorrect syntax near the keyword ‘WITH’
However, if I create a view for this query and reference the view in the dataflow instead, this works and I can preview the data:
Note, in the source you can also write a query referencing the view (shown below), this is useful if you require additional logic on top of the view.
As mentioned earlier, you can also write the query in a stored procedure and reference this in a similar way to the above. You should now be able to use this simple method to use CTEs in the source for a dataflow.
