1)
ID Name Aparment No
1001 XYZ 100
1002 ABC 101
1003 EFG 102
1004 IJK 103
1005 RST 104
2)
Name Aparment No Status Date Time
XYZ 100-1 Yes 01-07-2024 9:00 AM
ABC 101-1 No
EFG 102-1 No
3)
Aparment No Event Time
1004's Activated 01-07-2024 08:10
1005's Activated 01-07-2024 09:05
4)
ID Name Aparment No Checked In Excel2 DateTime Excel3DateTime
1001 XYZ 100 Yes 01-07-2024 09:00
1002 ABC 101 No
1003 EFG 102 No
1004 IJK 103 Yes 01-07-2024 08:10
1005 RST 104 Yes 01-07-2024 09:05
I want to create the consolidate excel as table 4 so table 1 is the master excel and need to cmap table 2 & 3 based on apartment no column digits and the status in table 2 defines residents who have checked in and table 3 Event column also defines residents who have checked in so want he output has table 4 using power automate desktop
I'm gettting the excepted outcome by the below method but is there is another efficent method which could give the faster output:
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Preethi Ambati\\Downloads\\MasterExcel.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Preethi Ambati\\Downloads\\Excel2.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance2
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance2 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData2
Excel.CloseExcel.Close Instance: ExcelInstance2
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Preethi Ambati\\Downloads\\Excel3.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance3
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance3 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData3
Excel.CloseExcel.Close Instance: ExcelInstance3
Variables.CreateNewDatatable InputTable: { ^['ID', 'Name', 'Apartment No', 'Checked In', 'Alarm Date TIme', 'Speak To Date Time'], [$'''''', $'''''', $'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
LOOP FOREACH CurrentItem IN ExcelData
SET ID TO CurrentItem['ID']
SET Name TO CurrentItem['Name']
SET ApartmentNo TO CurrentItem['Apartment No']
SET CheckedIn TO $'''No'''
SET AlarmDateTime TO $'''\'\''''
SET SpeakToDateTime TO $'''\'\''''
LOOP FOREACH CurrentItem2 IN ExcelData2
IF StartsWith(CurrentItem2['Apartment No'], ApartmentNo, False) THEN
IF CurrentItem2['Status'] = $'''Yes''' THEN
SET CheckedIn TO $'''Yes'''
SET AlarmDateTime TO $'''%CurrentItem2['Date']% %CurrentItem2['Time']%'''
END
END
END
LOOP FOREACH CurrentItem3 IN ExcelData3
IF StartsWith(CurrentItem3['Apartment No'], ApartmentNo, False) THEN
IF CurrentItem3['Event'] = $'''Activated''' THEN
SET CheckedIn TO $'''Yes'''
SET SpeakToDateTime TO CurrentItem3['Time']
END
END
END
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [ID, Name, ApartmentNo, CheckedIn, AlarmDateTime, SpeakToDateTime]
END
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: False Instance=> ExcelInstance4
Excel.WriteToExcel.WriteCell Instance: ExcelInstance4 Value: DataTable.ColumnHeadersRow Column: 1 Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance4 Value: DataTable Column: 1 Row: 2
DISABLE Excel.CloseExcel.CloseAndSaveAs Instance: ExcelInstance4 DocumentFormat: Excel.ExcelFormat.OpenXmlWorkbook DocumentPath: $'''C:\\Users\\Preethi Ambati\\Downloads\\Consolidated.xlsx'''
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance4 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData4
Variables.FilterDataTable DataTable: DataTable FilterParameters: { ^['RULE', 'COLUMN', 'OPERATOR', 'VALUE'], [$'''Or''', $'''Checked In''', $'''Equal''', $'''No'''] } FilteredDataTable=> FilteredDataTable
Variables.DeleteColumnFromDataTable.DeleteColumnWithName DataTable: DataTable ColumnName: $'''Alarm Date TIme'''
Variables.DeleteColumnFromDataTable.DeleteColumnWithName DataTable: DataTable ColumnName: $'''Speak To Date Time'''
54
