cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stuartemh
Frequent Visitor

Flow for Deleting rows older than 21 Days

Hi, I have an excel document on OneDrive for storing office visitor information. Each record takes up one row and has a date attached. I want to setup a flow to delete rows that are older than 21 days.

Are there any examples of how this might be done? Any help would be greatly appreciated.

Thanks

Stuart

10 REPLIES 10
manuelstgomes
Super User II
Super User II

Hi @stuartemh 

 

Yes perfectly doable. Here's the structure to achieve that:

2020-08-10 10_58_11-Flow - manuel.gomes@pestana.com - Outlook.png

 

The formula to calculate the date is the following:

addDays(utcNow(),-21)

 

Please note that the date comes from excel in a number format. Here's how to parse it:

https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/

 

Is this what you need?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Hi Manuel,

Does the date formatting need to be done before the Apply to each?

Also looking at your guide, request response is a premium function. Can the flow work without it?

stuartemh_1-1597059970070.png

Thanks

Stuart

 

Hi @stuartemh 

 

You can format it in the Condition with an expression. Just use formatDateTime('<date>','<dateformat>')

 

Regarding the template you can remove the trigger and the response and attach the middle in your Power Automate. All of that uses non-premium components so it should work fine.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Hi Manuel,

 

I'm getting an error when I run a test:

stuartemh_0-1597065969210.png

I'm using formatDateTime('<date>','<mm-dd-yyyy>') in the first part of the condition.

And addDays(utcNow(),-21) in the second.

 
 

This is what the flow now looks like.

stuartemh_0-1597066504620.png

 

Hi @stuartemh 

 

I was giving you the way to build the formula not the actual formula. You need to replace it with the variables for the data in the first field and the format in the second. 

 

So you need to use the variable in the Apply to Each and add it to the first, something like this:

 

formatDateTime(items('Apply_to_each')?['date'],'yyyyMMddThhmmssZ')

 

Makes sense? Then you can compare with the utcnow date on the other side..

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Hi @manuelstgomes,

 

I'm a bit confused with this to be honest. I'm testing the following flow:

stuartemh_0-1597138168631.png

With the condition 

formatDateTime(items('Apply_to_each')?['date'],'mm-dd-yyyy')

is equal to

addDays(utcNow(),-21)

 

This returns the error "Unable to process template language expressions for action 'Condition' at line '1' and column '14448': 'In function 'formatDateTime', the value provided for date time string '44048' was not valid. The datetime string must match ISO 8601 format.'."

 

Hi @stuartemh 

 

What Power Automate is telling you is that it cannot convert from a number (that excel returns as a date) to a valid date.

 

In my article, I explain how to do it, but it's quite easy:

https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/

 

Please add a compose before the formatDateTime to build the reference date:

formatDateTime('1-1-1900','dd-MM-yyyy')

 

and the use it the following formula in another compose:

addDays(outputs('Compose'),sub(items('Apply_to_each')?['date'],1))

 

Then use this compose in your condition, and you'll have two valid dates to compare.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Hi @manuelstgomes

 

Thanks the flow runs without error now. It isn't deleting the test rows in my spreadsheet though for some reason. When a new row is added into Excel, it adds the date in with the format mm-dd-yyyy . In my tests I am looking back 7 days: 

addDays(utcNow(),-7). Could this formula be looking at the mm, not the dd?
Please see the current setup below.

 

stuartemh_0-1597143377149.png

 
 
 
tutsfig
Frequent Visitor

Hi,

what if i need to delete a row with a date field value more than 2yrs old and schedule deletion once a year every Jan 1?

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,399)