Combining multiple excel sheets

[ashwinkolte]

Hi 

Need help with merging multiple excel sheets from a folder in power query . However below are the requirements 

1) The number of excel sheets in the folder could vary . It could be as high as 8-10 excel files.  

2) There is no pattern to the name of the excel files.  

3) In all the files , first column has exactly same data. This means the number of records in all files willl be exactly the same. However the name of the first column in each file is different . The first column datatype would be a text column 

3) All remining columns in all files (except the first column) , carry different type of data and have completely different signifcance  . Each file could have as many as 10-12 columns each

4) The user will keep files in the folder and on refresh , the model should be able to pick the files, whichever are present in the folder at that point in time  and merge them. (As already mentioned in point 1 , the number of files in the folder could vary from time to time ) 

5) Data cleansing could be required . Especially removing some blank rows in the files.   

Just to illustrate the above requirements , refer the screesnshot below for the required output 

Thanks

Ashwin

[amitchandak]

@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") )