Combining multiple excel sheets

ashwinkolte , Merge query is an option but that will for fixed sheets.

refer if this type of code with loop on table rows can work

Merge Queries, Why not Merge the code: https://youtu.be/YOFs39RCPfQ

Refer next video what I mean by table row for sheets

You have to all these operation in a column. I have used append.

But before append, you have two unpivot also in column  and then append pivot

After the promote header, unpivot in the same column

https://youtu.be/lIneR5VTW_8

unpivot other

Column name are different use , unpivot other

List.First(Table.ColumnNames("Last Step Table Name") )

Power Query Table.Unpivot, Table.UnpivotOtherColumns: https://youtu.be/0FEGEAz9UMw

Then Append as shown in video

and finally pivot

Pivot Data(Power Query) :https://www.youtube.com/watch?v=oKByyI09Bno&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=12

I doubt at some stage you need to Table.RenameColumns not List.First(Table.ColumnNames("Last Step Table Name") )