Summary:
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.
Importing Flow:
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.
Example Scenario:
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 Flow:
The trigger can be anything you want:
Button, Schedule, Anything!
Each Step (Actions):
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 --
Conclusion / How The Flow Works:
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
Want to know how this Flow works?
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:
addDays(utcNow(),variables('Days'))
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>']
item()?[variables('KeyColumn')]
Done!!
If you have any questions let me know. Thanks!
Hi @Jcook See the below screenshot oh how the excel file is set up. Date column is in date format (short date). This is just a test at the moment hence the made up names and numbers etc.
Hi JCook,
Firstly thanks for a great article. My team and I are based in Australia and are having a bit of trouble with this one. After a bit of tweaking (also reading all the comments) we can get the flow to run without errors. But the filter for a long time wouldn't return any rows legible for deletion. After changing the filter to 'less than' it now actually deletes, hurray!
However it is deleting everything and not excluding rows that it should be excluding. We think it might be to do with our time and date format in Australia. While we do use short date format, it's dd/MM/yyyy here and not MM/dd/yyyy. We think this might be the cause of our issue but haven't been able to nail it. Could you take a look at these screenshots and let me know what you think?
Thanks!