Forum Discussion
Editing window lost: PowerBI automatically prohibits changes in SQL Code
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
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.
9 Replies
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.
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 SourceThe 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 Sourceinstead 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 SourceI 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()
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! 😞
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" & "'"
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:
