Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |