Power query loads but errors on close and apply

Question

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?&nbsp;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 ')'.

v-lid-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;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&nbsp;so that statements like DECLARE, CTE will cause syntax error.&nbsp;Please refer to similar threads below:https://community.powerbi.com/t5/Desktop/Incorrect-syntax-in-Power-BI-but-query-works-fine-in-SSMS/td-p/45202https://community.powerbi.com/t5/Desktop/Custom-query-with-CTE-not-supported-bug/m-p/41395#U41395https://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/m-p/40983#U40983https://community.powerbi.com/t5/Desktop/Query-with-CTE-gives-error/td-p/22470&nbsp;Best regards,Community Support Team _ Dong LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Related content

Error 400 power query

Query Errors

Power Query Formula.Firewall Error in Desktop

Power Query Unexpected Error

kusto error- partial query failure