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!
You want to use this format in the same places as my Flow. So you want to add it to the Filter array, and the convert time zone action.
Might be easier if you download the flow and copy the actions over to your flow.
It won’t matter if your in Australia, since we are converting the today’s date, and the date from the Excel to match yyyy/MM/dd
When I configure your workflow, I end up deleting all rows as my date column may include no entries into the date column.
Would you happen to know a workaround that I could use to avoid this from happening?
I tried using less than and converting the Converted time to Round-trip date/time pattern and worked on deleting just the entries that had dates.
I am using this sheet as a daily upload that must be deleted after a certain time, therefore the table itself may have more rows than the number of entries.
Any help would be greatly appreciated.