Power Platform Community Forum Thread Details

Hello All,

The usecase is to connect to on-prem sql and get data using Power Automate. Execute a SQL query is not supported and the only way to get data is using Transform data using Power Query.

I am using Transform data using Power Query connector but I am not sure in what format I need to provide the query in new designer. The below query works fine when I connect to my on-prem db from excel and it also returns me the data

= let
Source = Sql.Database("SERVERNAME", "DB NAME"),
dbo_MYTABLE = Source{[Schema="dbo", Item="MYTABLE"]}[Data],
FilteredRows = Table.SelectRows(dbo_MYTABLE, each [ID] = "1706"),
Result = Table.SelectColumns(FilteredRows, {"ID"})
in
Result

but when I give the same query in the query input of Transform data using Power Query connector, it gives me error :

Failed to retrieve dynamic outputs. As a result, this operation's outputs might not be visible in subsequent actions. Error details: The dynamic operation request to API 'sql' operation 'SqlGetPassThroughPQMetadata' failed with status code 'BadRequest'. This may indicate invalid input parameters. Error response: { "error": { "code": 400, "message": "Error while processing request body: Unexpected character encountered while parsing value: E. Path '', line 0, position 0.", "source"....

I have tried the old designer but I am not sure how to pass a custom query in that.

Please can someone share, how to pass a simple custom query as input and get data.

Thanks in advance!

Categories:

Building flows