In Power Automate, when you want to connect to a SQL Server and if you have a On-Promise Gateway, then you cannot use the command “Execute a SQL Query” because it will say it’s not currently supported.
There is a workaround with “Transform data using Power Query” (ATTENTION: you cannot load it from a flow from a Solution… you’ll have to go to your Flows and edit the flow from there):

Let’s say we have 3 tables: ITEM_CATALOG, CATALOG and CURRENCY. We want to join them and filter them based on a variable found previously in our flow.
First, we can define our where
. Here I have several values that I want to test using a IN
:

I create a string with my different values separated by a coma.
Next, we can open the Power Query editor:

In the interface, we choose the 3 tables we need to merge and we add a parameter called “where”:

We rename it to “where” and leave the default settings:

Then we use the “Advance Editor”:

And we wrote the below:
let
where = Text.Split( "@{variables('where')}" , ",")
in
where
It means we want to split the variable “where” coming from the flow, based on the coma separator:

We can now merge the tables and add a filter:

And when the step to filter is here, we select “in” and our query:

Last step is to “Enable Load” to make sure this is what the operation will return to our flow:

You can run it to test and see if it works.
Then, to get the output from it, we’ll use a “Parse JSON”… The schema is probably something like:
{
"type": "object",
"properties": {
"resultType": {
"type": "string"
},
"value": {
"type": "array",
"items": {
"type": "object",
"properties": {
"COLUMN_A": {
"type": "string"
},
"COLUMN_B": {
"type": "integer"
},
"COLUMN_C": {
"type": "string"
}
},
"required": [
"COLUMN_A",
"COLUMN_B",
"COLUMN_C"
]
}
}
}
}
You may need to make several tries in order to find the correct schema. You can also use the “Generate from sample” by pasting the data from the previous step:

We use “value” in the loop:

And then we can access our columns:
