Retrieve SQL from applied steps?

[UlisesCiccola]

Hello everyone!

I have a question regarding power query. I have a query that has lots of transformations and would like to know if it is possible to retrieve the SQL code of these transformations to use later in MySQL or Oracle Database. Is this possible?

Just to get the select statement with all the transformations code and joins if applicable.

Thank you so much!!

[Jimmy801]

Hello @UlisesCiccola 

you can right click on your steps (till the step where query folding is taking place) and choose "view native query"

there are also software out that can trace the statement sent from power query to sql however.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[UlisesCiccola]

In my case the option of native query is grayed out? Which software (if possible free) can I use to retrieve this? 

Thank you so much @Jimmy801 !

[Icey · Accepted solution]

Hi @UlisesCiccola ,

Some notes from official document:

The View Native Query option is only available for certain relational DB/SQL generating connectors. It doesn't work for OData based connectors, for example, even though there is folding occurring on the backend. The Query Diagnostics feature is the best way to see what folding has occurred for non-SQL connectors (although the steps that fold aren't explicitly called out—you just see the resulting URL that was generated).

If the View Native Query option is not enabled (greyed out), this is evidence that all query steps cannot be folded. However, it could mean that a subset of steps can still be folded. Working backwards from the last step, you can check each step to see if the View Native Query option is enabled. If this is the case, then you have learned where, in the sequence of steps, that query folding could no longer be achieved.

Reference: Query folding # Determine when a query can be folded | Microsoft Docs

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Jimmy801]

Hello @UlisesCiccola 

did you check all your steps? If none showing the option then propably no query folding is taking place at all. Could you share your m-code? Maybe you have a step at the beginning that breaks query folding

BR

Jimmy