Unanswered

Deleting rows with future dates

(0) Share

Report

Posted on by bhargavdwadasi2

Hello,

I'm trying to automate the process of deleting rows in excel that have future date from the current date in the date column. And the current date is dynamic as this flow runs every week.

My main task is if the date in excel is ahead of current date then the row should get deleted

I have tried subtracting with ticks function but could get it.

I have used

formatDateTime(item()?['PI_EFC_DT'],'MM/dd/yyyy') in the left of the condition and

formatDateTime(utcNow(),'MM/dd/yyyy') on the RHS.

The date in my excel is store in dd/MM/yyyy format

please help me in this regard

@Sundeep_Malik can you please help me in this regard.

Thank you

Categories:

Business Process Flows

I have the same question (0)

All responses (6)

Answers (0)

Sundeep_Malik 6,484 on at

Like (0)

Report

Re: Deleting rows with future dates

@v-liwei-msft
@v-yujincui-msft
@v-qiaqi-msft
@Pstork1
@Expiscornovus

Need help here..

Was this reply helpful? Yes No
Pstork1 68,315 Most Valuable Professional on at

Like (1)

Report

Re: Deleting rows with future dates

I suspect that evaluating the date isn't the issue that you are facing. In my experience the problem in this kind of flow is trying to delete a row in Excel when you are running an Apply to Each loop on the same Excel table. Deleting the row throws off the array for the loop and it doesn't work. Instead you need to do it as a two step process.

1) Run the loop with the condition you have now. But instead of deleting the row when the condition is true append the key for the row to an array variable.

2) Run another loop on the array variable that has the keys of the rows to be deleted and use the delete rows action here.

That should work. If your still having issues please respond back with a description of the error or the specific problem and I'll try to help further.

Was this reply helpful? Yes No
bhargavdwadasi2 4 on at

Like (0)

Report

Re: Deleting rows with future dates

Thank you for the reply

I'm unable to put my mind around this can you please a rough sketch of the flow if possible.

thanks

Was this reply helpful? Yes No
Pstork1 68,315 Most Valuable Professional on at

Like (0)

Report

Re: Deleting rows with future dates

My field names don't match yours, but this should help.

2) Select rows to delete and store their key values in an array variable.

3) Use a second loop based on the array variable to delete each row based on the key. You'll have to type the JSON for the key into the Expressions tab.

Was this reply helpful? Yes No
bhargavdwadasi2 4 on at

Like (0)

Report

Re: Deleting rows with future dates

Thank you so much for the reply,

The flow is running with no errors but it is not deleting any rows with future dates.

my first row is with future date and when I saw the flow the condition in first apply yo each is failing, and my date format in excel is dd/mm/yyyy.
And is date storage the unique column.

Please help me in this regard

Many thanks

Was this reply helpful? Yes No
Pstork1 68,315 Most Valuable Professional on at

Like (0)

Report

Re: Deleting rows with future dates

I would suggest leaving the format unspecified in the formatdatetimes. That way it will default to ISO 8601. If you specify a specific format its going to take the dates as strings for comparison. If you look at my comparison you'll see that I didn't use formatdatetime() at all. And mine is tested and works.

Was this reply helpful? Yes No