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