09-29-2020 09:04 AM
This Flow deletes Excel Rows Older than X amount of days based on a Date column in Excel. The Excel file can be in SharePoint or OneDrive. Very little configuration is needed by the user to make this work.
When importing the Flow, you might get an error message. This is because the Excel file that the Flow is being pointed to does not exist in your Tenant. Click 'Save as new flow' if you get this message.
I have a Excel file that has 2 columns,
My Name Field is my unique Key Column.
Date Hired, I will be checking and deleting all records that have a Date Hired older than 7 days.
** Your Date column MUST be in Date format in Excel **
Here is what my Excel file looks like.
The trigger can be anything you want:
Button, Schedule, Anything!
All 3 Variables need to be inputted by the user:
Days: Enter number of days you want the Flow to check against the date column to delete. For example, -7 will delete all records 8 days old
** Don't forget to put the - **
KeyColumn: Enter your Key Column from Excel. This column MUST have unique values.
In my Excel file this column name is Name Field
DateColumn: Enter the name of your Date Column from Excel
In my Excel file this column name is Date Hired
Past Date: Only change the Destination time zone field. Change to your time zone
List rows present in table: Only fill in required information marked with red *
** If your Excel file has more than 1500 records. Click the 3 dots on the List rows action and select settings. Than up the value on the Threshold and click done. **
Filter array - Records older than X: Do not touch anything in here
For each record older than X: The loop itself do not touch
Delete a row: Fill in ONLY -- Location, Document Library, File, Table --
That is it, some important things to consider:
- Make sure your Key Column has unique values
- Change the trigger to your liking
- Don't forget to add the minus symbol in the Days Variable
- Only change the values highlighted in Yellow
- Make sure to check the amount of records in your Excel file and configure Threshold values as needed
- Your Date column in Excel MUST be formatted as Date
We get todays date using utcNow() expression, than add days based on the amount of days entered into the Days variable. This date is converted to local time, and formatted like yyyy/MM/dd. This is to do easy comparison/evaluation.
The expression I use is:
We list ALL rows in Excel because Excel connector has limitations on the Filters we can use. Also the Excel Connector does not like spaces in the Column names. Which is why Filter array is used instead.
The Filter array grabs the Converted date from the first step and checks if the date is greater than the Excel date.
Note the Excel date comes over as a serial date in Power Automate. This is a long Integer, so to convert the serial date into a date we can read and use, we use this expression:
addDays('1899-12-30', int(item()?[variables('DateColumn')]), 'yyyy/MM/dd')
Now in the Apply to each loop, we use the values returned from the Filter array (Which is only records older than X date)
We use a Delete a row action to delete the record in Excel.
I am using the item() expression to select the Key Value from the row inside the Loop.
The syntax of this is:
item()?['<Excel Column Name>']
If you have any questions let me know. Thanks!
Hi, thank you for posting this flow. I looked for something like this almost a year ago and could not find it.
I ran into one issue with the Filter step when I initially tried to set this up. I was eventually able to get it to work but do not understand how because the formula did not change as far as I can see. Yesterday, all the flows popped up with the same error I saw at first after weeks of working fine. This is the error message:
The execution of template action 'Filter_array_-_Records_older_than_X' failed: The evaluation of 'query' action 'where' expression '@greater(body('Past_Date'), addDays('1899-12-30', int(item()?[variables('DateColumn')]), 'yyyy-MM-dd'))' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.
Is this something you have seen before with this flow? The Error Details pane tells me it is a new issue.
Have you seen this issue before?
What is the Date/Time that you're using. The format is important. Also note Microsoft has updated the Excel Connector (Depending on your region etc. Your tenant should get the update soon) The update is for the excel action to bring the dates over as actual dates.
Right now (Or previously) the excel dates came over as a serial number example: 45887621. The workaround was to add days to this number and convert to the real date format.
This update might have broken this Flow. I will have to check this out over the weekend.
If the Flow is broken, all we need to do is update Filter array - Records older than X: Remove the addDays() expression and replace with
I will confirm this over the weekend though.
Thanks for the information!
Your fix worked! I appreciate the quick response.
Update 1/25 - It worked to get rid of the alert but the flow is no longer deleting older items.
Another update as I continue to try to get these flows up and running again. I could not get the flow to actually delete items with the changed formula. I noticed that the excel inputs still had the 5 digit date number so I went back to the original formula. It ran without errors and deleted everything, including newer items. I am still working on it so we will see.
Microsoft reverted the update after our other conversation due to an ISO bug in the date format.
You can now choose which time format you wish to use in the dropdown field
Good to know! This required three adjustments to resolve.
1 - Filter Array = replace with item formula you posted.
2 - List Rows Present Date Time Format = ISO 8601
3 - Past Date Format String = Round-Trip Date/Time Pattern
Hi. Many thanks for the guide and template. I have followed the guide but I get the following error message when trying to save the flow:
Any help would be greatly appreciated. I am quite new to using Power Automate.
Thanks in advance.
Could you post a screenshot of the flow. The error message suggests that the KeyColumn variable does not exist.
please post a screenshot with the variables expanded.
Thanks for the response@Jcook. I started the process over and it saved fine the 2nd time, however, now whenever I run the test I get the following error message: