Search code examples
powerbiexpressionpower-automate

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

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

enter image description here

enter image description here

Solution

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

    Flow

    Result

    Result

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