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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

DerWindy_0-1664355624499.png

then you can change the code in the window here:

DerWindy_1-1664355659466.png

Then I click on OK tp apply my change.

Normally the Code in Advanced Editor looks like this:

DerWindy_2-1664355714634.png

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

DerWindy_3-1664355756265.png

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)

DerWindy_4-1664355837206.png

 

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

1 ACCEPTED SOLUTION
hokeson
Employee
Employee

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. 

View solution in original post

9 REPLIES 9
hokeson
Employee
Employee

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
Not applicable

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: 

DerWindy_0-1664547550283.png

 

Anonymous
Not applicable

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" & "'" 

 

Anonymous
Not applicable

Hi Daryl,

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

but anyway - thanks for your help and the workaround! 

Anonymous
Not applicable

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
Frequent Visitor

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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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



 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors