Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

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

Posted on December 1, 2021December 5, 2021 by Tom

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

Power Automate Excel highest value

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.

Zero spam, unsubscribe anytime.

2 thoughts on “How to get the highest value from Excel rows in Power Automate”

  1. Anrik says:
    February 28, 2023 at 10:27 pm

    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

    Reply
    1. Tom says:
      March 12, 2023 at 11:21 am

      Hello Anrik,
      you probably didn’t switch the ‘Select’ action to the ‘text only mode’ using the small button on the right side.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

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.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes