Manipulate Native Query SQL

[zua]

Hello, 

I develop a custom connector for a database which provides query folding and native query.

This database provides special functionalities in SQL queries that I want to use in the connector. 

Is it possible to access the SQL query of a step in the editor and manipulate it? Like when I right click on a step in the editor and see the native query?  

For example: I need to provide a string after a SQL statement (SELECT * FROM TEST {STRING})).

This string should be filled when a function is called, otherwise it should be empty.

The function should be callable in the power query editor. 

Is this somehow possible?

Thx!  

[jennratten]

If you are wanting to use SQL to perform the transformations instead of M (Power Query) then you can simply include that into your SQL statement in the Source step.  

[zua]

Hi, 

thank you @Anonymous.

And is it possible to access the SQL code that is used in Power Query Steps?

These would be standard functions 

like Table.SelectRows or Table.RenameColumns? 

[Anonymous]

Hi @zua ,

To manipulate the SQL query of a step in the Power Query Editor, you would typically need to implement a custom column or a custom function that allows you to insert your additional string into the SQL statement. However, Power Query does not allow direct manipulation of the native SQL queries through the Editor's UI.
Instead, you can define your custom function in the M language that takes a SQL statement and a string as inputs and returns the modified SQL statement. For example:

let
    AppendStringToSQL = (sqlStatement as text, appendString as text) as text =>
        let
            modifiedStatement = sqlStatement & " " & appendString
        in
            modifiedStatement
in
    AppendStringToSQL

You would then call this function in your query steps where needed.

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