cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Sort Excel rows and select the first element

Hi there,

 

Scenario: we have a list of tasks that should be done in the form of an Excel table. One of the columns is called "LastUpdate". This column is modified after each modification. Now I would my flow to find the oldest task in the table, send myself a reminder and then update the "LastUpdate" timestamp to "now".

 

What I have done sofar: Via the Excel GetRows action, I can find all, a subset or even only one row(s). But this only works (fine) if the data is already sorted in the Excel table. But if I update the "LastUpdate" timestamp manually and don't apply the sorting again in Excel, the Flow will not return the right row.

 

Problem: I am thus searching for a way to filter the rows based on the timestamp and then pick the first element. What would be the simplest way to do this? Any idea?

 

Thanks,

Tim

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Sort Excel rows and select the first element

So, I was able to filter the data using a "Data Operations Filter Array" action with the following formula:

 

@empty(item()?['AskedOn'])

This effectively removes all the rows where the "AskedOn" column is not empty.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Sort Excel rows and select the first element

Hi @TimBourguignon,

 

Currently, Excel doesn’t support Odata query options. There is no function can be used to sort the results. I have seen a similar request on Flow ideas Forum, please vote the idea at here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Excel-ODATA-Filtering-obsolete-request/idi-p/11568

 

Besides, Odata queries are supported in SharePoint, when using SharePoint list, we could use $orderby to sort returned items. Please check this doc for more details:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-...

 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Sort Excel rows and select the first element

Any idea how I could best go around this limitation? Should I transform my rows into some other data type and then filter? Should I loop over all the records and "remember" the one I want to pick? Any other idea?

Re: Sort Excel rows and select the first element

So, I was able to filter the data using a "Data Operations Filter Array" action with the following formula:

 

@empty(item()?['AskedOn'])

This effectively removes all the rows where the "AskedOn" column is not empty.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 253 members 6,543 guests
Please welcome our newest community members: