Forum Discussion
Combining multiple excel sheets
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
1 Reply
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
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") )
