cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisggates
Regular Visitor

Setting up a recurrence flow that happens once each week that will delete all rows in a spreadsheet that are 7 days old.

I am needing to reset a spreadsheet (delete every row that is from the previous week) every week to only have submissions from a MS Form for the current week. I have it set to trigger with the recurrence trigger, but I cannot figure out how to make a condition based on the Completion Time column in my spreadsheet. I know this is possible with the expression functions, but I'm very new to the expressions so any help would be greatly appreciated. My screenshots below are what I have done so far. 

 

chrisggates_0-1609942803199.pngchrisggates_1-1609942856530.png

chrisggates_2-1609943025128.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

Ok, here's the flow to do what you have requested.

1) Use a recurrence trigger set to run once a week.  Mine will run every Saturday.  List the Rows currently in the table.

image.png

2) Here's the first tricky part.  Excel will provide the dates as a decimal number of days after 1/1/1900.  So you need to create a number that represents 7 days ago. and filter the List of Rows based on that number.  To calculate the number use this formula. Ticks is the number of milliseconds after 1/1/1000. So divide that by 864,000,000,000 to get the number of days since then and subtract 693,593 days, which is the number of days between 1/1/1000 and 1/1/1900.  This is all to get the number of days represented by 7 days ago.

Add(Div(Ticks(AddDays(UTCnow(),-7)),864000000000),-693593)

 Now use that to Filter the list of Rows using that date and parse the result so you have access to each filtered row.

image.png

3) Finally use a Loop through the filtered results and delete each row using a Key,  In my example I'm assuming ID is unique.  This works because you are looping through the filtered list, not through the table rows themselves.  So when you delete a Row the row is removed from the table, but not the Filtered list.

image.png

I tested it and it does work.



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

View solution in original post

3 REPLIES 3
Pstork1
Dual Super User III
Dual Super User III

Assuming you have a value in the Excel sheet for each row that represents the timestamp this is relatively straightforward.  The biggest issue is that you can't delete rows in Excel from inside an Apply to Each loop.  The problem is that if you delete the row that way you'll short-circuit the logic controlling the loop.  The standard workaround is to use a do until loop to loop through the records and delete the ones that need to be deleted.



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

Can you help with the expressions needed for the condition to accurately identify the correct rows to delete? The Excel sheet does have a value for each row that represents the timestamp. 

chrisggates_0-1609948109433.png

 

 

Pstork1
Dual Super User III
Dual Super User III

Ok, here's the flow to do what you have requested.

1) Use a recurrence trigger set to run once a week.  Mine will run every Saturday.  List the Rows currently in the table.

image.png

2) Here's the first tricky part.  Excel will provide the dates as a decimal number of days after 1/1/1900.  So you need to create a number that represents 7 days ago. and filter the List of Rows based on that number.  To calculate the number use this formula. Ticks is the number of milliseconds after 1/1/1000. So divide that by 864,000,000,000 to get the number of days since then and subtract 693,593 days, which is the number of days between 1/1/1000 and 1/1/1900.  This is all to get the number of days represented by 7 days ago.

Add(Div(Ticks(AddDays(UTCnow(),-7)),864000000000),-693593)

 Now use that to Filter the list of Rows using that date and parse the result so you have access to each filtered row.

image.png

3) Finally use a Loop through the filtered results and delete each row using a Key,  In my example I'm assuming ID is unique.  This works because you are looping through the filtered list, not through the table rows themselves.  So when you delete a Row the row is removed from the table, but not the Filtered list.

image.png

I tested it and it does work.



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

View solution in original post

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.

Top Solution Authors
Users online (1,277)