I was having an issue with my SQL query running in Power BI. I am trying to use a simple common table expression and my code works fine in SQL Server but fails when I try to copy and paste my query into the Advanced Editor in Power BI. Nothing is wrong with the connection I have to the database as other queries I’ve written work fine.
Even something as simple as this works when I preview the output but not after applying the changes:
with new_tbl as (select top 10 * from my_tbl)
select top 10 * from new_tbl
It gives me a syntax error with this code when I try to run it saying I need to terminate anything before the with statement using a semicolon. I have tried that too but it hasn’t resolved the issue, is there something I’m missing when transferring code from SQL to Power BI?