cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Yacob_hard
Level: Powered On

Delete rows from a Excel sheet where the date is not current

Hi All,

 

I'm working with a flow on a recurrence basis, the flow should;

 

1. Grab the file from the sharepoint drive

2. Attach the file to an email to be sent out

3. After the email has been sent, the excel spreadsheet should have all rows with dates older the present date removed

 

I've managed the getting and emailing of the file, however a bit stumped when looking at how to only remove the rows that have been added if the dates don't match up.

 

Here's what i'm working with now, any advice would be greatly appreciated!

 

The spreadsheet where i want to remove all data other than the headers;

Email stats example.png

 

Following the Emailing of the file;

 

List rows.pngdelete rows.pngformatDateTime(utcNow(), 'YYYY-MM-DD')

 

Hope this makes sense!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Yacob_hard
Level: Powered On

Re: Delete rows from a Excel sheet where the date is not current

Hi @v-xida-msft,

 

Yes, i'd like to remove all rows where the Date is not current after the days email has been sent out.

 

Rather than have the delete action before the email goes out, i'm instead looking at having the delete action at the end of the flow, would that be possible?

 

 

 

Edit your flow_1.pngEdit your flow_2.pngEdit your flow_3.png

 

 

I've just tested this flow which seems to work as expected but would be good to get another pair of eyes across this.

 

The Conditional expression was a great help though!

 

Thanks!

 

 

5 REPLIES 5
Community Support Team
Community Support Team

Re: Delete rows from a Excel sheet where the date is not current

Hi @Yacob_hard,

 

Could you please share a full screenshot of your flow's configuration?

Do you want to remove rows whose date (Date & Time column value) is older than current date?

 

I have created a Excel file within my SharePoint library and the data structure of it as below:24.JPG

 

I have made a test on my side and please take a try with the following workaround:

  • Add a "Recurrence" trigger, Interval set to 1 and Frequency set to Day.
  • Add a "List rows present in a table" action, specify your Excel file.
  • Add a "Apply to each" action, input parameter set to output of the "List rows present in a table" action.
  • Within "Apply to each" action, add a Condition, click "Edit in advanced mode", type the following formula:
@less(formatDateTime(item()?['Date & Time'], 'MM/dd/yyyy'), utcNow('MM/dd/yyyy'))

Within "If/yes" branch of Condition, add a "Delete a row" action, Key Column field set to Date & Time, Key Value field set to Date & Time dynamic content of the "List rows present in a table" action.

 

  • Under "Apply to each" action, add a "Get file metadata" action, specify Site Address and File Identifier field set to your Excel file.
  • Add a "Get file content" action, specify Site Address and File Identifier field set to Id dynamic content of the "Get file metadata" action.
  • Add a "Send an email" action, Attachments Name - 1 field set to DisplayName dynamic content of the "Get file metadata" action, Attachments Content - 1 field set to File Content dynamic content of the "Get file content" action.

Image reference:25.JPG

 

26.JPG

 

27.JPG

The flow works successfully as  below:28.JPG

 

29.JPG

 

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yacob_hard
Level: Powered On

Re: Delete rows from a Excel sheet where the date is not current

Hi @v-xida-msft,

 

Yes, i'd like to remove all rows where the Date is not current after the days email has been sent out.

 

Rather than have the delete action before the email goes out, i'm instead looking at having the delete action at the end of the flow, would that be possible?

 

 

 

Edit your flow_1.pngEdit your flow_2.pngEdit your flow_3.png

 

 

I've just tested this flow which seems to work as expected but would be good to get another pair of eyes across this.

 

The Conditional expression was a great help though!

 

Thanks!

 

 

Yacob_hard
Level: Powered On

Re: Delete rows from a Excel sheet where the date is not current

Thanks, I moved the deletion of data above the actual sending of the report to after the rows were deleted which seems to be working better now.

 

@v-xida-msft I was wondering if it's possible to return a numerical value within the email that is sent out based on the amound of rows present in each table -1 (headers row).

 

 

Administrator
Administrator

Re: Delete rows from a Excel sheet where the date is not current

Hello, @Yacob_hard!

 

Have you had a chance to apply @v-xida-msft‘s recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

 

 

Thank you for being an active member of the Flow Community!

 

-Gabriel

Flow Community Manager

-Gabriel
Microsoft Flow Community Manager
Yacob_hard
Level: Powered On

Re: Delete rows from a Excel sheet where the date is not current

Hey @GabrielStJohn,

 

This did work well, however because we're processing 700+ emails a day, i often found that the AWS services would throw a 400 error and be unable to find the sheet in sharepoint.

 

I've since abandoned this as it doesn't seem this is a scalable solution to tracking inbound and outbound mails from a shared mailbox.

 

Any alternate suggestions would be most welcome! Smiley Happy

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 49 members 4,750 guests
Please welcome our newest community members: