I apologize. I’m on my phone, so I may be shooting some from the hip on some syntax here. I don’t know Power BI, but I’ll give you the Excel answer and the logic will be the same:
Attach to Excel SheetA
Get %FirstFreeRowA% %FirstFreeColumnA%
Read from Excel A1 to %FirstFreeColumnA - 1% %FirstFreeRowA - 1% to %ExcelDataA% (with a title row)
Attach to Excel SheetB
Get %FirstFreeRowB% %FirstFreeColumnB%
Read from Excel A1 to %FirstFreeColumnB - 1% %FirstFreeRowB - 1% to %ExcelDataB% (with a title row)
Loop 0 to %ExcelDataA.RowsCount - 1% increment of 1 As %LoopIndexA%
If %ExcelDataA[LoopIndexA][‘Status’]=Closed
Next
Else
Set variable %CurrentID% to %ExcelDataA[LoopIndexA][‘ID’]%
Loop 0 to %ExcelDataB.RowsCount - 1% increment of 1 As %LoopIndexB%
Attach to ExcelB
If %CurrentID% = %ExcelDataB[LoopIndexB][‘ID’]
If %ExcelDataB[LoopIndexB][‘Status’]=Closed
Set variable %CloseDateB% to %ExcelData[LoopIndexB][‘date’]
Attach to ExcelA
Write to Excel ‘Closed’ Row=%LoopIndexA + 1% Column=B
Write to Excel %CloseDateB% Row=%LoopIndexA + 1% Column=C
END(IF)
END(IF)
END(LOOP)
END(IF)
END(LOOP)
‘as of now, parts 1 and 2 are done, we just need to get new rows of B added to the bottom of A.
Set variable %LastRowA% to %FirstFreeRowA - 1%
Loop 0 to %ExcelDataB.RowsCount - 1% increment of 1 As %LoopIndexB%
Set variable %CurrentID% to %ExcelDataB[LoopIndexB][‘ID’]%
Loop 0 to %ExcelDataA.RowsCount - 1% increment of 1 As %LoopIndexA%
Attach to ExcelA
If %CurrentID% = %ExcelDataA[LoopIndexA][‘ID’]
Go To Label ‘Match’
END(IF)
END(LOOP)
Set variable %IDB% to %ExcelDataB[LoopIndexB][‘ID’]
Set variable %StatusB% to %ExcelDataB[LoopIndexB][‘Status’]
Set variable %DateB% to %ExcelDataB[LoopIndexB][‘date’]
Write to ExcelA %IDB% to Row=%LastRowA + 1% Column=A
Write to ExcelA %StatusB% to Row=%LastRowA + 1% Column=B
Write to ExcelA %DateB% to Row=%LastRowA + 1% Column=C
Set variable %LastRowA% to %LastRowA + 1%
Label ‘Match’
End(Loop)
