Editing window lost: PowerBI automatically prohibits changes in SQL Code

[Anonymous]

Hi,

I use the HANA Connector to get Data from our SAP HANA system. This Connector works with SQL Queries. It is (or better to say was) possible to change the SQL code within the window that opens up if you click on the Gear symbol in applied steps.

then you can change the code in the window here:

Then I click on OK tp apply my change.

Normally the Code in Advanced Editor looks like this:

But now, after the last release, everytime i apply a change in the SQL Code, it looks like this 

And then it is not possible to change anything anymore in the SQL window, because the gear symbol is gone (The Value... Prefix seems to deactivate the gear wheel)

Now I have to rewrite the Query in the Advanced Editor (delete the Statement Value.NativeQuery and change something more, what takes time, is repetitive and unneccassary) to get my Gear back to do some more changes or reverse some changes that delivered bad data. 

My Question: This is already a big pain after day one of work because every change now forces me to undo the code changes manually. What happens here and how can I prevent PowerBI doing this little "modification" of my code that I didn´t ask for?

How can I stop PowerBI to insert the "Value.NativeQuery" Command? I´ve never had that problem before

Thanks for your help - it is urgent

[hokeson · Accepted solution]

This change in behavior will ultimately benefit you. PBI now support folding on top of SAP HANA native queries, and you will most likely see improved performance and additional scenarios unlocked.

The missing cog will be fixed in PBI desktop November 2022 version.

It is preferable to stay on PBI desktop August version to edit easily select statements.

While using PBI desktop September 2022 version it is neccessary to update On-premisses Data Gateway to at least August 2022 version and you can use the workaround mentioned above.

My personal recomendation is to upgrade gateway and stay with PBI desktop August version till November version is released. 

[Anonymous]

Okay Daryl your solution works fine in PBI Desktop

the problem is now that in PBI Service Reports qith Queries with this new prefix cannot be updated, they throw a gateway meshup error. maybe my gateway has an older version?

but even with your solution it does not update in the service

my gateway version is: 

[Anonymous]

Thanks Daryl

The problem with this is, that after my Query works i add a string that data is captured dynamically (for example I exchange DATE >= '2020' into DATE >= '"&Number.ToText(Date.Year(DateTime.LocalNow()) -2)&"') I think I cannot use this in your suggestion.

than maybe I will have to do that in SQL in the future if MS doesn´t fix anything. It would still be my favourite to make an option out of this query folding and not something you just have to accept! 😞

[Daryl-Lynch-Bzy]

Hi @Anonymous  - can you add variable and parameters to the sql string.  Could the following scenario work?

#"Parameter Value" = DateTime.FixedLocalNow(),
#"Parameter Date" = Date.From( #"Parameter Value" ),
#"Parameter Transformed" = Date.ToText( #"Parameter Date" , "yyyymmdd"),
sql = "Select [Column] from Table Where [Column] = '" & #"Parameter Transformed" & "'" 

[Anonymous]

Hi Daryl,

thanks. I think that can work. just much more effort than before 😕

but anyway - thanks for your help and the workaround! 

[Anonymous]

some things are important to know if you want to use this way:

If you have any " in your SQL query (like xyz AS "XYZ Column") than you have to add an additional " to that statement (AS "XYZ Column" --> AS ""XYZ Column""). otherwise the advanced Editor can´t read it and you get an error because the editor thinks after your first " the variable statement sql = "" is over.

 othersie it works fine but still MS please make query folding optional, not mendatory, thanks!

[venudharm]

I too am facing this issue. Report fails to refresh in service with "Native queries aren't supported by this value" error. I could see that prior versions of desktop <=aug2022 working fine without value.nativequery()

[Daryl-Lynch-Bzy]

Hi @Anonymous - it looks like this might be known bug that Microsoft are working on.  But I would still recommend the above, I use it all the time for SQL and MDX queries.

[Daryl-Lynch-Bzy]

Hi @Anonymous  Microsoft has added the Native query component to make easier to apply Query Folding in the following Applied Steps.  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