cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yacob_hard
Advocate I
Advocate I

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.pngformatDateTime(utcNow(), 'YYYY-MM-DD')formatDateTime(utcNow(), 'YYYY-MM-DD')

 

Hope this makes sense!

 

1 ACCEPTED SOLUTION

Accepted Solutions

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!

 

 

View solution in original post

5 REPLIES 5
v-xida-msft
Community Support
Community Support

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.

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!

 

 

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

 

 

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
Community Manager
Power Automate | Power Virtual Agents
Super User Program Manager



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! 🙂

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (5,407)