HANA Advanced SQL statement being changed to NativeQuery. Unable to edit SQL directly

Hi GaryOJ,

I finally got to talk to Microsoft and this "feature" introduced in the September 2022 patch.  The support guy gave me a link to the August Patched version and after I installed this the issue went away.

I assume they will realize this is a bug and fix it in a future patch.  The support guy said maybe in October but I'm not sure how he knows this.    

The work-around shown by TheCreepster1 works but you have to be careful about what you replace.  However I'd say unistall Power BI and then install the August Patch version.    

When they fix this I'll post the details here.

Regards

Russell

Hi russellya ,

You could edit the code in Advanced Editor.

Query overview in Power BI Desktop - Power BI | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Russell,

I just managed to work around the problem by going to the advanced editor and reverting it back to the old format at the front and end of the query, so removed the Value.NativeQuery( from the start and , null, [EnableFolding=true]) from the end, and replaced with something like the below:

From

To

This then made the cog re-appear and enabled more simplistic editing of the SQL query.

russellya - I glad Microsoft are working on the bug.  

I can understand how this can be frustrating because it make that useful cog disappear.  However, I would like to suggest another approach that might make is easier to maintain the Hana Query.  I would suggest inserting an earlier Applied Step to hold the SQL script.  This would look like this:

let
   sql = "SELECT TOP 100 * FROM TABLE",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

The sql step is visible in the Power Query screen, so you can simply paste in new strings into the formula bar. 

However, I like to go one step further.  I recommend pasting the sql string in the Advance Editor because it will eliminate the auto addition of #(lf) and #(tab).  Something like this:

let
   sql = 
"
SELECT TOP 100
 * 
FROM 
   TABLE
",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

instead of 

let
   sql = "SELECT TOP 100 #(lf)#(tab)*#(lf)FROM#(lf)#(tab)TABLE",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

Super crucial to get this fixed/reverted.
Some of us working in corporate environment don't exactly have an option to revert to the older version due to IT/Admin settings 🙂

This is still happening in the Nov release. I had to go to the AUG version to make the reports work.