Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Simonawu
Frequent Visitor

SQL scripts boxed in Value.NativeQuery automatically

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.

Simonawu_0-1666922918406.png
@ImkeF @BA_Pete 

 

1 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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.

Simonawu_0-1666938945079.png

 

 

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.