Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
  • hokeson's avatar
    hokeson
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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! 😞

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Daryl,

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

    but anyway - thanks for your help and the workaround! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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!