Get Max value from an array

(0) Share

Report

Posted on by Koense

Hi Community,

Please advice me on the following. For a flow I'm trying to get the max value for a selection of rows.

The data in excel is as follows.

Item	Vendor	Price
ABC	123	1,5
ABC	456	5
DEF	123	2

I'm trying to achieve 2 things:
For an item get the Maximum price and the Average price.

The average price I've solved but i'm struggeling with the maximum price.
So first I am getting all the results in my data set filtering on the item.

Then I'm selecting the prices column.

The output of the select is as follows:

[

"3",

"11"

]

To get the average my flow reads as follows (If any suggestions for optimalisation please feel free to add).

Now this gets me the average price.

If I try a similar expression for the max it fails.

max(outputs('List_rows_present_in_a_table')?['body'])

I assume this is because the values of the array are non decimal.

That makes sense I am however struggeling to correct this.
Using the XML output (as I do for the average) also results in an error.

Please advice on how the get the maximum purchase price.
Thanks in advance,

Koen

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

Expiscornovus 33,828 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Koense,

For the Maximum price, I would use a float with a local in your Select. After that you should be able to use the max function on the body of the Select.

Try something like below.

Map field in text mode

float(item()?['Price'], 'nl-nl')

Expression in Compose action

max(body('Select'))

Was this reply helpful? Yes No
Koense 12 on at

Like (0)

Report
Copy link

Link copied!
Hi Expiscornovus,

Thanks a lot for the suggestion. I do however run into problems with the float.
The output for value is similar to this:
{
"@odata.etag": "",
"ItemInternalId": "aadc12ca-99cc-4156-bd89-89fd39ccc000",
"Product": "S6050",
"Price": "3",
},
{
"@odata.etag": "",
"ItemInternalId": "a87e462a-1c88-487b-bf2d-3d2243c90c6d",
"Product": "S6050",
"Price": "11",
}

When trying to add "Price" as dynamic content to the float it doesn't show up as an option (just body & value).
When using body/value it returns invalid:

InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '@float(outputs('List_rows_present_in_a_table')?['body/value'])' failed: 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

When trying to point directly to the price column
float(outputs('List_rows_present_in_a_table')?['body/value']?['Price])
it also fails:
InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '@float(outputs('List_rows_present_in_a_table')?['body/value']?['Price'])' failed: 'The template language expression 'float(outputs('List_rows_present_in_a_table')?['body/value']?['Price'])' cannot be evaluated because property 'Price' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

Please advice.

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,828 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @Koense

Within a Select Map field you cannot refer to the whole array. You can use the item()?['NameOfField'] syntax instead.

Did you try the expression I suggested earlier?

float(item()?['Price'], 'nl-nl')

Was this reply helpful? Yes No
Koense 12 on at

Like (0)

Report
Copy link

Link copied!

Thanks @Expiscornovus ,
I should think less and listen more 🙂

Was this reply helpful? Yes No