Update Excel if it exist else create a new row where both source and destination are excel in a SharePoint

[heyjay]

Hello experts,

I need help with my flow.

I have an excel source file in a SharePoint where I need to first filter 2 columns, 1st is the ID column where I only get non blank and 2nd is the status column where I only get Pending, Approved and Not Approved.
After filtering, I have to compare the filtered result to the destination excel which is also in the same SharePoint, all of those that matches will have to be updated and all of those no match will be created as new into the destination file. After which, all of those Pending in status in the destination will generate a notification email for each row. 

I cant get my flow working.

Any help will be greatly appreciated. 

[v-yueyun-msft · Accepted solution]

Hi , @heyjay 

According to your description, you want to update the second the Excel file based on the first Excel and if the row do not exist based on the “ID” and “status” column , you want to add the column in the second Excel file.

This is my test Excel file, for your need ,we need to add an Index column(unique column) in the second Excel file due to the “Update column” action need a unique column if exits the duplicate value.

Solved: Update multiple rows in "Update a row" action with... - Power Platform Community (microsoft....

And this is my test Excel file:

This is my flow:

(1)The first three actions:

@and( not(equals(item()?['ID'], string(''))),contains(createArray('Pending','Approved','Not Approved'), item()?['status']))

(2)The Apply to each action:

[Note] The Index is the unique column I created in Excel file. As above said "due to the “Update column” action need a unique column if exits the duplicate value."

You could create this column manually or you could use the Office script:
Run Office Scripts with Power Automate - Office Scripts | Microsoft Learn

And this is the code:
function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  let tbl = selectedSheet.getTable("Table2")

  tbl.addColumn(0).setName("Index")

  let rowCount = tbl.getRowCount()+1;

  let firstColumnRange = selectedSheet.getUsedRange().getAddress().split("!")[1].split(":")[0].split('')[0]

  let firstRowRange = selectedSheet.getUsedRange().getAddress().split("!")[1].split(":")[0].split('')[1]

  selectedSheet.getRange(`${firstColumnRange}${Number(firstRowRange) + 1}`).setValue("1");

  selectedSheet.getRange(`${firstColumnRange}${Number(firstRowRange)+1}`).autoFill(`${firstColumnRange}${Number(firstRowRange) + 1}:${firstColumnRange}${Number(firstRowRange)+rowCount-1}`, ExcelScript.AutoFillType.fillSeries);

}

Best Regards,

Yueyun Zhang

[heyjay]

Hello @v-yueyun-msft ,

This is brilliant, so you are! Thank you for helping! This has solved my problem.