Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello, I have a sql server query that loads in power query but kicks errors when I attempt to close and apply. The query runs fine in SSMS and loads correctly in power query but throws errors I would expect to see while using CTEs (when I am actually using temp tables). Can anyone tell me why this error is happening only when I try to close and apply?
Error:
Microsoft SQL: Incorrect syntax near the keyword 'into'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.
Solved! Go to Solution.
Hi @Anonymous ,
Power BI Desktop has issues on parsing Queries in Direct Query mode that the dataset is treated as a derived table and is illegal in the context of direct query so that statements like DECLARE, CTE will cause syntax error.
Please refer to similar threads below:
https://community.powerbi.com/t5/Desktop/Custom-query-with-CTE-not-supported-bug/m-p/41395#U41395
https://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/m-p/40983#U40983
https://community.powerbi.com/t5/Desktop/Query-with-CTE-gives-error/td-p/22470
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Power BI Desktop has issues on parsing Queries in Direct Query mode that the dataset is treated as a derived table and is illegal in the context of direct query so that statements like DECLARE, CTE will cause syntax error.
Please refer to similar threads below:
https://community.powerbi.com/t5/Desktop/Custom-query-with-CTE-not-supported-bug/m-p/41395#U41395
https://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/m-p/40983#U40983
https://community.powerbi.com/t5/Desktop/Query-with-CTE-gives-error/td-p/22470
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |