“I use Power Automate to process results of a Forms quiz, what’s the best approach to get the highest points value from the results Excel file?”
The easiest way to get the highest value is to use the sorting as when searching in a SharePoint list. But in some situations that’s not possible. You can sort only columns with a single word header. It’s not a problem with SharePoint as all spaces are replaced by the code _x0020_, but it can be a problem when working with an Excel file. Excel file doesn’t replace spaces in the headers. If it contains a space, it stays as a space, and as such you can’t sort by the column.
Don’t use a loop
The first idea you might have could be to use a variable and a loop. Loop through all the values, and each time you encounter higher value, update the variable.
Don’t use that solution. It’s a dirty solution that will consume a lot of API calls and that might run very long if there’re many rows.
Use the max(…) expression
A better solution is to use the max(…) expression that’ll give you a maximum from an array of numbers. But you don’t have an array of numbers at this moment. You have an array of objects (the rows from the Excel file). Therefore, you must extract only the numbers first. And since the Excel data is provided as a string, you must convert them to an integer too.
Add a ‘Select’ action in your flow to select only the numbers, in this example the values from ‘Total Points’. As mentioned above, you should also convert the value into integer in this step.
int(item()?['Total points'])
The output of the ‘Select’ will be a single array that’ll contain only the numbers. Apply the max(…) expression on it and you’ll get the highest available value.
max(body('Select'))
Summary
Getting the highest value from an Excel file is not the most efficient process in Power Automate. Once the column header contains multiple words you lose the only available functionality to do an easy sorting. But if you understand how to convert the values into a simple array, a single expression can give you what you need.
Of course, the easiest solution would be to change the header name, but that’s not always an option.
Hi,
I’m not getting an array of [123,124,125]
I get an error on the Compose step: InvalidTemplate. Unable to process template language expressions in action ‘Compose’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘max’ expects all of its parameters to be either integer or decimal numbers. Found invalid parameter types: ‘Object’.’.
My Excel sheet has the column RequestID with the row values: 123, 124, 125
The Select int(item()?[‘RequestID’]) returns:
[{“123”: “”}, {“124”: “”}, {“125”: “”}]
Which means the Compose fails
Hello Anrik,
you probably didn’t switch the ‘Select’ action to the ‘text only mode’ using the small button on the right side.
Hello, how about if the Excel field is a date? How can i obtain the highest date from a column and compare is with utc(now)?
Hello Mihaela,
Excel returns dates as a serial numbers so the approach is the same, you can then compare this number with the corresponding today’s number as explained here: https://tomriha.com/how-to-send-a-reminder-on-a-date-stored-in-excel-file-with-power-automate/