Answered

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

(1) Share

Report

Posted on by bkeano

136

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

1-Filter Transform.JPG

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (1)

yashag2255 24,769 Super User 2024 Season 1 on at

Like (2)

Report

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/idi-p/211409#M11967

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!

Was this reply helpful? Yes No
Automater 13 on at

Like (0)

Report

I get access denied when I click your link.

Was this reply helpful? Yes No
Verified answer

leinaDRbt 18 on at

Like (3)

Report
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.

» Power Automate_...

Was this reply helpful? Yes No
lashotoSP 9 on at

Like (0)

Report

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-power-automate-varible

Was this reply helpful? Yes No
lashotoSP 9 on at

Like (0)

Report

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

Was this reply helpful? Yes No
leinaDRbt 18 on at

Like (0)

Report

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

Was this reply helpful? Yes No
bkeano 136 on at

Like (0)

Report

@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

Was this reply helpful? Yes No