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?
2
The issue you are facing is due to the power of BI handling SQL queries. I had the same problem with my cross-team.
The error message you see is because Power BI is trying to execute your query as a subquery and the SQL server requires that any statements preceding a WITH
clause be terminated with a semicolon.
Here is the working example for you:
;WITH new_tbl AS (SELECT TOP 10 * FROM my_tbl)
SELECT TOP 10 * FROM new_tbl
This will ensure the any preceding statements are properly terminated.
4