SQL scripts boxed in Value.NativeQuery automatically

[Simonawu]

Dear All,

We upgrated the PBI desktop from Jan 2022 to Sep 2022 Version this month, When we edit data in power query, we found that the SQL query scripts would be boxed in Value.NativeQuery automatically, and then we could not click the step option on the right to edit the SQL.
The datasource is SAP BW view and Could anybody told us how to set NativeQuery to be off by default, thank you so much.

@ImkeF @BA_Pete 

[BA_Pete]

Hi @Simonawu ,

Rather than trying to remove the Value.NativeQuery function, I would recommend just going to the Home tab > Advanced Editor when your query is selected to be able to view/edit the entire query code.

The first time you do this, you'll see that your SQL script is full of '#(lf)', '#(cr)' and '#(tab)' as Power Query is trying to send your script formatting to the source. You can delete all these and just format the script as you want it in Advanced Editor. The script will stay formatted this way in Advanced Editor once saved, and the source will receive and parse it just fine.

Pete

[Simonawu]

Dear Pete，
Thanks for your reply.
I tried to delete all these Special characters like '#(lf)', '#(cr)'  in Advanced Editor, data can be excuted but I still cannot find the Settings option Window under the Applied Steps to edit the SQL code.
This is very inconvenient for us if we need to view or modify SQL scripts. 
Besides, we are using the Power BI Report server. There are other problems with the May 2022 version, so it is not suitable for us to rollback to the previous version.

[BA_Pete · Accepted solution]

If you really want the cog back instead of just using Advanced Editor then, near the end of the Value.NativeQuery code, you should see an argument like '[EnableFolding=true]'. If you delete this, you should get the cog back.

However, I'd strongly recommend that you keep folding enabled for obvious reasons, and just use the Advanced Editor to edit your SQL as required.

Pete

[Simonawu]

Dear  Pete,
Thanks for your reply.
I will consider it according to the real situation, thank you so much