How to get the highest value from Excel rows in Power Automate

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

---

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

First Name

Email address

Zero spam, unsubscribe anytime.

Leave this field empty if you’re human: