Showing results for 
Search instead for 
Did you mean: 
Advocate III
Advocate III

SQL action 'Get rows (V2)' Aggregation transformation field major bug

This is a bug with the following Power Automate Cloud action:  SQL Get rows (V2) and how table columns are handled when specifying an Aggregation transformation and a column name in the Select Query field referencing the column alias created in the Aggregation transformation field. Here is a screenshot of how the action is setup prior to the test run:


When running this in a simple test flow, it works as expected.  But when running this exact same action, and I mean, with the exact same settings and params in the production flow that has a lot more steps to it, then it fails miserably with an error message that does not point to any inkling of a solution. To wit:




The API 'sql' returned an invalid response for workflow operation 'SELECT01::check_if_REVISION_CODE_in_ENERGY' of type 'OpenApiConnection'. Error details: 'The API operation 'GetItems_V2' is missing required property 'body/value/0/rev_code'.'




And yes, the rev_code column exists in the table and contains no NULL values.  The column is of SQL Server DATE type.  When testing on another table with a column of type BIGINT, it works in the production flow.  But AGAIN, on the original table with the column of type DATE, it works in the test flow, so why not in the production flow?

One last thing to note: when selecting Peek code in the action menu, one can see this for the production flow:



    "inputs": {
        "host": {
            "connectionName": "shared_sql",
            "operationId": "GetItems_V2",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
        "parameters": {
            "server": "default",
            "database": "default",
            "table": "[dbo].[ENERGY]",
            "$apply": "aggregate(rev_code with max as max_rev_code)",
            "$select": "max_rev_code"



 which is exactly the same for the test flow, except for the "metadata" keys, which have different "operationMetadataId" values.


I am open to suggestion as this is the end of the road for me, and God forbid, perhaps someone at Microsoft in tech support familiar with this product issue could chime in and actually help users become more productive because as it stands, if adding up all the hours wasted on the this, I could have written a dozen flows in Python instead!


Helpful resources

Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (1,363)