Can I pass a value to SQL Transform data using Power Query?

[bkeano]

HI

Don't see a way to do this and I know I can always filter after the fact, but is there a way to use my intial trigger to filter ( so like a parameter) on the SQL flow item 'Transform data using Power Query'.

The reason I ask is that the number of transactions been mashed up in the Transform flow item will increase over time and perhaps slow down or make the flow a bit unweildly, plus it's increasing resource usage againts a already heavilty interogated database

As per below screenshot I'm using 'Filter Array' afterwards instead and then using the output values from that further down my flow, this achieves the result I want, but I guess it would be much more efficient if I could use the trigger directly in Transform

[yashag2255]

Hi @bkeano 

Passing dynamic content to a Power Query is not doable as per current capabilties. There is an idea on the forum, please upvote so that it can be considered as a potential feature in the future releases.

Idea: https://powerusers.microsoft.com/t5/Flow-Ideas/Add-ability-to-load-dynamic-content-in-Power-Query/id...

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Automater]

I get access denied when I click your link.

[leinaDRbt · Accepted solution]

TLDR;

Flow variable name: 'TestVar' - Type String

You can use it like this:

Table.SelectRows(#"Navigation 1", each [username] = "@{variables('TestVar')}")

The "@{variables('TestVar')}" is the crux here.

I found a very nice solution to this problem provided by (Twitter @AymKdn / GitHub @Aymkdn) in his blogpost power-automate-execute-a-sql-query-via-on-promise-gateway.

My intention is to spread this solution, that every one can benefit from it.
I've attached a dump of the blogpost, if the site is unreachable for what ever reason.

[lashotoSP]

There is a solution, but it is not mine.

Essentially initialize a Power Automate Flow variable, set it to what you are looking to filter your SQL by, and enter your variable into your query as follows:

"@{variables('TestVar')}"

In my SQL query, I have a DECLARE statement that is set to equal "@{variables('TestVar')}" and then run my query based on that SQL variable.

It will look like an error, but it will work if you just update it anyway. Also save this syntax because when you try to look at it again later, Power Query will not be able to recreate it and it will look like your variable is set to equal a blank. And then you'll have to look for this thread for a full hour to remember. Ask me how I know

https://stackoverflow.com/questions/74554443/how-to-query-on-premises-sql-server-database-using-powe...

[lashotoSP]

How is this not marked as the solution? lol I just posted the same thing

[leinaDRbt]

Maybe @bkeano  didn't see the updates yet, since it's a 4 years old question.
The Stackoverflow answer you referenced was written by me

[bkeano]

@leinaDRbt Tested this and works perfect! My scenario was a bit diferent, used
 let where = "@{variables('where')}" in where

as I was looking for just one value