Power Automate, get the max/biggest value from an output array

Question

New to Power Automate and struggling with an expression.

My flow basically takes multiple data from a PBI report and sends it as a http post.

Before I do the post I need to validate some data, one data item being 'distance' so what I am trying to do is before I do the http post I set a variable and try and get the biggest distance from the data from PBI then If it's greater than a certain amount do something in a condition.

I can't get the max expression to work, it just throws an error, the only time I've had it working it would only get the first item in the PBI data array not the biggest.

Error I receive is...

Unable to process template language expressions in action 'Set_variable' inputs at line '0' and column '0': 'The template language function 'first' expects its parameter be an array or a string. The provided value is of type 'Null'.

PBI data comes as...

[
  {
    "JobId": 5267,
    "id": 625,
    "Distance (km)": 13.64878414881468
  },
  {
    "JobId": 5267,
    "id": 1178,
    "Distance (km)": 20.659444721556447
  },
  {
    "JobId": 5267,
    "id": 1343,
    "Distance (km)": 8.097056529573832
  }
]

The expression I am using is...

max(outputs('GetPBIData')?[0]?['Distance (km)'])

I need to get the highest distance value from the whole array and check it is within tolerance.

Any ideas on best way to achieve this expression?

See image for reference of my flow...

Answer 1

This is the OOTB concept ...

1. Compose Array - This is your data.
2. Initialize Max Object - This gets the first item in the array in order to initialise it. The expression is first(outputs('Compose_Array'))
3. For Each Array Item - Loops through each item in the Compose Array data. Note: You must set this to be sequential, i.e. set the concurrency to 1.
4. If Distance GT Distance On Max Object - This is a Condition operation. The left hand side of the expression is item()['Distance (km)'], the operand is is greater than and the right hand side variables('Max Object')['Distance (km)'].
5. Set Max Object - If the distance is greater than the current max object, it will set that variable to the current item. Expression = item()
6. Compose Result - This is superfluous. It's to just show the result but from this, you can extract the distance and do you threshold comparison.

Result

There is an easier, single step way to do this but it will require the use of a premium connector.

Answer 2

I found this solution.

It seem you have to manual find your max value by:

1. init MaximunValue
2. Apply to each output then set MaximunValue = newValue if newValue > current MaximunValue
3. After Apply to each block you will get MaximunValue

Answer 3

If you want it all in a single step, the Advanced Data Operations connector is by far the best connector to use.

There are a raft of other very useful operations that help you cut down the steps in your flows along with things that are simply not possible in standard operations in PowerAutomate.

Expert Operation

This allows you to run a SQL statement over your data array.

Configured Step

Data

{
  "Data": @{outputs('Compose_Array')}
}

Query String

SELECT *
FROM [Data]
ORDER BY [Distance (km)] DESC
LIMIT 1

Result

[
  {
    "Distance (km)": 20.6594447215564,
    "JobId": 5267,
    "id": 1178
  }
]

You'll get an array of data back but then all you need to do is use the first() expression to get that given you know you're only ever going to return a single row.

Note: Take note that the decimals will be truncated given the way the operation works. You have a lot of decimal places, it's not a common thing but obviously a possible scenario so just be aware of that.

There are other operations in that connector that will also work for you, like Transform but this is the simplest way to do it.

The Sort Object Array operation didn't like your data having properties with brackets and spaces so unfortunately, that was out of the question but it retained the decimal places once I removed those.