Posted by “We are screwed!”
me at the moment of technology induced panic
That was the feeling when I saw the below error messages.
The client’s setup is an on-prem SQL server exposed to the cloud via an on-prem Gateway. It works perfectly fine for exiting Dataflow integration, but this time we needed data from a function in Power Automate.
Now problem, you can use SQL server connector, right?
Almost (but yes, if you read this post or knew before).
Set back #1
You cannot use “Execute a SQL query (V2)” with an on-prem gateway connection. Not supported!
Sure, let’s then use “Execute stored procedure (V2)”.
Set back #2
Apparently, you cannot return dataset from the function. Can call it if it was just a function or procedure without return value. But if it returns data – bad luck!
Panic mode on!
Wait, but what if…
Power Query to the rescue! Again.
The good news, you still CAN return data from a function (or stored proc) in on-prem gateway, but you need to use “Transform data using Power Query”!
Just click the “Edit query” button and you’ll see the list of available functions or procedures for your connection and database (assuming you have set it up). In my case the function required a parameter “as_at_date” which I can either enter a value for.
That’s it for the case to return a dataset – here you go:
At the same time, there is so much more you can do with the data using power query! Just to name a few things you can do in Power Query as one action in your flow, just to name a few:
- Add calculated fields, change type of existing fields etc
- Join dataset with from other tables/functions and aggregate data
- Transpose, reverse rows, replace values, fill values and other transformation magic
Explore and create!
DISCLAIMER:
Unfortunately, it’s not possible (as of Sep 2021) to pass a value from the flow into the DB function if one requires it as on the screenshot above. You can vote for this idea to be added one day (maybe) here: https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Add-ability-to-load-dynamic-content-in-Power-Query/idi-p/211409
CloudMinded 
Hi Andrew, do you happen to know if a parameter value can be passed to that query from Power Automate? Or how else a parameter embedded in the SQL query could be edited at runtime?
LikeLike
Unfortunately, it’s not possible (as of Sep 2021) to pass a value from the flow into the DB function if one requires it as on the screenshot above. You can vote for this idea to be added one day (maybe) here: https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Add-ability-to-load-dynamic-content-in-Power-Query/idi-p/211409
LikeLike
Hi Sheldon,
Yes, we use to pass a date into it – see a screenshot in the post: https://technomancycomau.files.wordpress.com/2021/02/screen-shot-2021-02-02-at-4.49.02-pm.png?w=2048
LikeLike
This is brilliant, thanks!
LikeLike
Hi,
I would like to do the same as Sheldon but I can’t see anything in the screenshot that suggests how to send a variable value from Power Automate to the function.
Essentially it feels like I should be able to populate that ‘as_at_date’ text box with a value from Power automate.
Otherwise how will the parameter for the function ever get populated?
LikeLike
Unfortunately, it’s not possible (as of Sep 2021) to pass a value from the flow into the DB function if one requires it as on the screenshot above. You can vote for this idea to be added one day (maybe) here: https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Add-ability-to-load-dynamic-content-in-Power-Query/idi-p/211409
LikeLike
Flow variable name: TestVar – Type String
M-Code:
Table.SelectRows(#”Navigation 1″, each [username] = “@{variables(‘TestVar’)}”)
“@{variables(‘TestVar’)}” is the crux here.
It will show the preview as literal string of “@{variables(‘TestVar’)}” during design time and thus return no relsults on your filtered column. But it’ll work during runtime.
Sometimes after running and editing the flow again, the “@{variables(‘TestVar’)}” can disappear. Just re-enter it and it’ll be fine.
Detailed guide here:
https://stackoverflow.com/questions/74554443/how-to-query-on-premises-sql-server-database-using-power-automate-varible
LikeLiked by 1 person