Skip to main content

Notifications

Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : 6/YyLX3e/3aEQIUrDYbKc9
Power Platform Community / Forums / Power Automate - Building Flows / Copy Excel row then de...
Power Automate - Building Flows
Unanswered

Copy Excel row then delete row in first table

Like (0) ShareShare
ReportReport
Posted on 14 Jun 2023 16:50:17 by KelLew88 10

Hi

 

So I have a list of premises in a spreadsheet (Premise_Targetting.xls) which staff need to visit over the next few years. Staff are required to input the time and date they attended, the outcome of the visit and the staff members name. I have set this up using data validation to ensure data quality. 

 

I have created a flow which works on a schedule (overnight) once a day. It basically takes rows from Table 1 that have been completed, and copys them to table 2. BUT it's then supposed to delete the record from Table 1.

 

See attached 

 

So the flow works perfectly until it needs to delete all those that have 'Yes' in the QA column (these have been flagged as having passed the QA process). Have i used the Delete a Row correctly? Why is it failing on the delete a row? 

 

Has anyone else done this successfully? 

 

Any help would be greatly appreciated. one.JPGtwo.JPGThree.JPG

  • v-wenjuan-msft Profile Picture
    v-wenjuan-msft Microsoft Employee on 23 Jun 2023 at 01:53:01
    Re: Copy Excel row then delete row in first table

    Hi @KelLew88 ,

     

    May I know is there any progress or do you need any further help?

     

    Community Support Team _ Wenjuan Zou

    If this post helps, then please consider Accept it as the solution to help the other members find it.

  • v-wenjuan-msft Profile Picture
    v-wenjuan-msft Microsoft Employee on 20 Jun 2023 at 02:40:00
    Re: Copy Excel row then delete row in first table

    Hi @KelLew88 ,

     

    Sorry but I'm not expert on Excel...I do a research online and find something may help under your scenario.

    Solved: unlock excel file with Flow - Power Platform Community (microsoft.com)

    How to clear annoying Excel file locks in Power Automate – CleverWorkarounds

     

    For "data validation rule" part, still I'm not expert on Excel. To narrow down the issue, you may check flow run's "Add a new row into a table" outputs to see whether flow pass those data to table 2. If yes, that's mean flow has sent out the request but somehow the Excel refuse the data maybe for the mentioned "data validation rule"... If so, you may need to cancel the rule once you want the flow running well...

     

    Community Support Team _ Wenjuan Zou

    If this post helps, then please consider Accept it as the solution to help the other members find it.

  • KelLew88 Profile Picture
    KelLew88 10 on 18 Jun 2023 at 20:26:20
    Re: Copy Excel row then delete row in first table

    I have also just noticed that my date and time fields are not being moved across to the new table in the flow, even though they were complete. E.g. we have 3 visit dates/times fields. These do not all have to be completed if the job was done at the first or second visit, so there are blanks sometimes. I have checked my flow and all fields are mapped to the right columns in the second table. so can't work out why they are not being added at the Add Row into table step. Could it be because there is a data validation rule on these date/time fields in table 1?

     

    Any ideas would be welcome 🙂

     

    Thanks

  • KelLew88 Profile Picture
    KelLew88 10 on 18 Jun 2023 at 20:12:46
    Re: Copy Excel row then delete row in first table

    Thank you! I have a slight issue i've discovered though. Flow now works thanks for your suggestion but my issue is that the spreadsheet is protected with a password. 

     

    Is it possible to "Run a Script" which unlocks the spreadsheet within the flow. I've noticed that if the sheet is protected the flow fails at the "delete a row". But when it's not protected it runs successfully. Logically i understand that this is probably not possible but I'm hoping there might be a work around?

     

    Thanks

  • v-wenjuan-msft Profile Picture
    v-wenjuan-msft Microsoft Employee on 16 Jun 2023 at 09:52:36
    Re: Copy Excel row then delete row in first table

    Hi @KelLew88 ,

     

    You need to insert in "Key column" with a column filling with unique value. Otherwise, the "Delete a row" will retrieve several rows and cause the issue.

     

    To fix it, you can add an "ID" column in your first list filling with unique number for each row.

    Delete your "Apply to each 2" and add "Delete a row" right after "add a row into a table". Choose "ID" column as key column and insert dynamic content of "ID" column into "Key value".

     

    Community Support Team _ Wenjuan Zou

    If this post helps, then please consider Accept it as the solution to help the other members find it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,700 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,015 Most Valuable Professional

Leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans