0

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

Improve this question
3
  • Do you want the rest of the data related to the max row? Commented Feb 22, 2024 at 8:04
  • OOTB, the answer given is as good as it gets, if you want a smarter solution with less steps, let me know. It will require the use of a third party connector though. Commented Feb 22, 2024 at 8:26
  • "Do you want the rest of the data related to the max row?" not for now but it would be useful yes Commented Feb 22, 2024 at 9:42

4 Answers 4

Reset to default
1

I recommend first doing a sort on the array using the sort variable as the variable you want the max for, then create a new array variable using function sort (eixstingArray, 'Distance (km)') then the max will be at the end of the array.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is the best / most concise option in this thread
0

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.

Improve this answer

6 Comments

Thank you, I am using premium connectors and was hoping to do a single step rather than for each, thank you for already taken the time you have so far to post that, that's amazing thank you.
There's a catch, but I'll add another answer with a single step approach.
I have added an image of my current flow.
Yeah, Max() doesn't work like that unfortunately. It works like it does in Excel. You need to pass it a whole heap of values so you'd first need to convert all of your data to a primitive array. The Advanced Data Operations connector can do that for you using the C# Execute Script operation or it could just find the max for you easily as it is. That's another answer I could give you now I think about it. :-D
Amazing thank you so much, I have this working. Top work this.
|
-1

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
Improve this answer

Comments

-1

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

Expert

Result

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.

Improve this answer

4 Comments

Amazing, working through it now, when I add the Expert Data Op step it asks for a connection name and advanced data op api key, do you know what these are?
Ignore my last comment, I have found it, it looks like a premium non microsoft connector with a seperate billing model which I may struggle to use
Correct ...! :-) It's still cheap as but hey, if you can't you can't. I live by it. You can sign up for a trial to see if it's worth it anyway, no harm in doing that.
No doubt it looks fantastic yeah

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.