Query error when applied to Power BI, but works on Powe Query.

[FBiazotto]

Greetings. I'm new to Power BI and I'm starting with a simple query that works in PowerQuery returning the result, but when I apply to Power BI I get the following error: "Incorrect syntax near the keyword 'set'.Incorrect syntax near') '."

Query:

set dateformat dmy
select b.nome Setor , COUNT (a.setor) TOTAL, a.COMPETENCIA from movimento a
inner join setor b on (a.setor = b.setor)
where DATAHORA between '01/07/2020' and '31/07/2020 23:59:59'
and TIPOMOV = 'S'
and Localiza = '1'
group by b.nome , a.setor , a.COMPETENCIA
order by COUNT (a.setor) desc

Error: Incorrect syntax near the keyword 'set'.Incorrect syntax near') '.

[edhans · Accepted solution]

I would avoid keying in SQL statements directly into the advanced editor. It breaks further folding. If you want to key in SQL, create a view. Otherwise, use the Power Query UI to generate your steps. In many cases, it will fold a query back to the SQL server for you.

Additionally, there are security issues with advanced SQL statements in the service requiring higher privileges to run them.

[edhans · Accepted solution]

I would avoid keying in SQL statements directly into the advanced editor. It breaks further folding. If you want to key in SQL, create a view. Otherwise, use the Power Query UI to generate your steps. In many cases, it will fold a query back to the SQL server for you.

Additionally, there are security issues with advanced SQL statements in the service requiring higher privileges to run them.

[FBiazotto]

I'll try, thanks!

[edhans · Accepted solution]

Great. Post back with specifics if you need help.

When you are building your steps in Power Query, on the right side where APPLIED STEPS are, right-click on a step and if you see "View Native Query" you will know that a SQL statement is being generated and sent back to the server so it can do the work for you.