Skip to main content
Power Automate
    • Robotic process automation
    • Power Automate Desktop
    • Process advisor
    • AI Builder
    • Connectors
    • Templates
  • Pricing
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Register
    • ·
    • Sign In
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Community News
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Connecting To Data
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Share, Chat, Discuss
    • Community Chat & Announcements
    • Power Automate Cookbook
    • Off Topic Discussion
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Watch & Share Videos
    • Webinars and Video Gallery
    • MBAS Gallery 2019
    • MBAS Gallery 2020
    • Give Feedback & Share Ideas
    • Power Automate Ideas
    • Report Issues
    • I Found A Bug
    • Find & Join Local User Groups
    • Power Automate User Groups
    • Read & Share Blogs
    • Power Automate Community Blog
    • Community Blog Topic Suggestions
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power Automate Community
    • Forums
    • Share, Chat, Discuss
    • Power Automate Cookbook
    • Delete Excel Rows X Amount of Days Old

    Delete Excel Rows X Amount of Days Old

    09-29-2020 09:04 AM

    Super User III Jcook
    Super User III
    453 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Jcook
    Super User III Jcook
    Super User III
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Delete Excel Rows X Amount of Days Old

    ‎09-29-2020 09:04 AM

    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.

    Errors.png

     

    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.

    Jcook_1-1601391723566.png

     

    The Flow:

    Jcook_0-1601391408199.png

     

    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

     

    Variables.png

     

    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. **

    Date and Excel List All Rows.png

     

     

    Filter array - Records older than X: Do not touch anything in here

    Jcook_3-1601352177109.png

     

     

    For each record older than X: The loop itself do not touch

    Delete a row: Fill in ONLY -- Location, Document Library, File, Table --

    Apply to Each Loop.png

     

    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!





    Did I answer your question? Mark my post as a solution!

    If you like my post please hit the Thumbs Up


    Proud to be a Flownaut!


    Check out my blog for Power Automate tips, tricks, and guides
    FlowAltDelete

    Follow @FlowAltDelete


    ExcelDeleteRecordsOlderThanXDaysTemplate_20200929160047.zip
    Labels:
    • Labels:
    • Automated flows
    • Button Flows
    • Scheduled flows
    Message 1 of 4
    453 Views
    1 Kudo
    Reply
    • All posts
    • Previous Topic
    • Next Topic
    lwaheed
    lwaheed
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    yesterday

    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? 

    Message 2 of 4
    22 Views
    0 Kudos
    Reply
    Jcook
    Super User III Jcook
    Super User III
    In response to lwaheed
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    yesterday

    Hi @lwaheed 

     

    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 

    item()?[variables('DateColumn')]

     

    I will confirm this over the weekend though.

     

    Thanks for the information!





    Did I answer your question? Mark my post as a solution!

    If you like my post please hit the Thumbs Up


    Proud to be a Flownaut!


    Check out my blog for Power Automate tips, tricks, and guides
    FlowAltDelete

    Follow @FlowAltDelete


    Message 3 of 4
    18 Views
    1 Kudo
    Reply
    lwaheed
    lwaheed
    New Member
    In response to Jcook
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    yesterday

    Your fix worked! I appreciate the quick response. 

    Message 4 of 4
    17 Views
    0 Kudos
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents
    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing
    • © 2021 Microsoft
    • Twitter
    • RSS Feed
    • YouTube
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions