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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors