Combine Inconsistent Excel File - Each File Consist three Sheets

[Saad_Hanif456]

Dear Experts,

I need to combine the Excel files from the folder through power query on the following condition

1. Each files have three sheets and each file has a difference column sequence

2. The data below column headers should populated in the same column header where it exists in the excel sheet.

3.  Column Headers of Jan Excel File is

Jan - Column Headers (One Excel File three Sheets)
S.No	ISB DS	PRB DS	SRB DS
1	S.No.	S.No.	S.No.
2	Employee Index	Employee Index	Employee Index
3	Employee Id	Employee Id	Employee Id
4	Employee No	Employee No	Employee No
5	Employee Name	Employee Name	Employee Name
6	Designation	Designation	Designation
7	DOJ	DOJ	DOJ
8	DOL	DOL	DOL
9	Unit	Unit	Unit
10	Department	Department	Department
11	Sub Department	Sub Department	Sub Department
12	Client Branch	Client Branch	Client Branch
13	Division	Division	Division
14	Region	Region	Region
15	City	City	City
16	Province	Province	Province
17	Territory	Territory	Territory
18	Location	Location	Location
19	Remarks	Remarks	Remarks
20	Salary Per Month	Salary Per Month	Salary Per Month
21	Current Salary	Current Salary	Current Salary
22	MEAL ALLOW	MEAL ALLOW	MEAL ALLOW
23	Add / Del	Add / Del	Add / Del
24	OT Hours	OT Hours	OT Hours
25	OT Rate	OT Rate	OT Rate
26	OT Amount	OT Amount	OT Amount
27	Travel Allowance	Travel Exp.	Travel Exp.
28	Travel Exp.	Sp.OT Amount	Sp.OT Amount
29	Sp.OT Amount	Working Days	Working Days
30	Working Days	Extra Days	Extra Days
31	Extra Days	Extra Days Amount	Extra Days Amount
32	Extra Days Amount	Ex-Gratia	Ex-Gratia
33	Ex-Gratia	Other Arrears	Other Arrears
34	Other Arrears	Hardship Allowance	Hardship Allowance
35	Hardship Allowance	Veh Rep/Maint	Veh Rep/Maint
36	Other Exp	Travel Allowance	Travel Allowance
37	Absent Days	LaonAdj	LaonAdj
38	Absent Days Amount	Other Exp	Other Exp
39	EOBI	Absent Days	Absent Days
40	EOBI Employer Arrears	Absent Days Amount	Absent Days Amount
41	Edu. CESS	Notice Salary	Notice Salary
42	ESSI	EOBI	EOBI
43	Life Ins.	EOBI Employer Arrears	EOBI Employer Arrears
44	Health Insurance	Edu. CESS	Edu. CESS
45	3rd Party Liab.	ESSI	ESSI
46	Bank Charges	Life Ins.	Life Ins.
47	Mobile Allowance	Health Insurance	Health Insurance
48	Misc.	3rd Party Liab.	3rd Party Liab.
49	LaonAdj	Bank Charges	Bank Charges
50	Gross Total	Mobile Allowance	Mobile Allowance
51	Service Charges	Misc.	Misc.
52	General Sales Tax	Gross Total	Gross Total
53	Billing Amount	Service Charges	Service Charges
54	Refund To Client	General Sales Tax	General Sales Tax
55	I.Tax	Billing Amount	Billing Amount
56	Professional Tax	I.Tax	I.Tax
57	EOBI Emp Con	Loan Ded.	Loan Ded.
58	Net Salary	Professional Tax	Professional Tax
59		EOBI Emp Con	EOBI Emp Con
60		EOBI Employee Arrears	EOBI Employee Arrears
61		Net Salary	Net Salary

5. Column Headers for Feb Excel File is

Feb - Column Headers (One Excel File three Sheets)
S.No	ISB DS	PRB DS	SRB DS
1	S.No.	S.No.	S.No.
2	Employee Index	Employee Index	Employee Index
3	Employee Id	Employee Id	Employee Id
4	Employee No	Employee No	Employee No
5	Employee Name	Employee Name	Employee Name
6	Designation	Designation	Designation
7	DOJ	DOJ	DOJ
8	DOL	DOL	DOL
9	Unit	Unit	Unit
10	Department	Department	Department
11	Sub Department	Sub Department	Sub Department
12	Client Branch	Client Branch	Client Branch
13	Division	Division	Division
14	Region	Region	Region
15	City	City	City
16	Province	Province	Province
17	Territory	Territory	Territory
18	Location	Location	Location
19	Remarks	Remarks	Remarks
20	Salary Per Month	Salary Per Month	Salary Per Month
21	Current Salary	Current Salary	Current Salary
22	MEAL ALLOW	MEAL ALLOW	MEAL ALLOW
23	Add / Del	Add / Del	Add / Del
24	OT Hours	OT Hours	OT Hours
25	OT Rate	OT Rate	OT Rate
26	OT Amount	OT Amount	OT Amount
27	Travel Allowance	Travel Exp.	Travel Exp.
28	Travel Exp.	Sp.OT Amount	Sp.OT Amount
29	Sp.OT Amount	Working Days	Working Days
30	Working Days	Extra Days	Extra Days
31	Extra Days	Extra Days Amount	Extra Days Amount
32	Extra Days Amount	Ex-Gratia	Ex-Gratia
33	Ex-Gratia	Other Arrears	Other Arrears
34	Other Arrears	Hardship Allowance	Hardship Allowance
35	Hardship Allowance	Veh Rep/Maint	Veh Rep/Maint
36	Other Exp	Travel Allowance	Travel Allowance
37	Absent Days	LaonAdj	LaonAdj
38	Absent Days Amount	Other Exp	Other Exp
39	EOBI	Absent Days	Absent Days
40	EOBI Employer Arrears	Absent Days Amount	Absent Days Amount
41	Edu. CESS	Notice Salary	Notice Salary
42	ESSI	EOBI	EOBI
43	ESSI Arrears	EOBI Employer Arrears	EOBI Employer Arrears
44	Life Ins.	Edu. CESS	Edu. CESS
45	Health Insurance	ESSI	ESSI
46	3rd Party Liab.	ESSI Arrears	ESSI Arrears
47	Bank Charges	Life Ins.	Life Ins.
48	Mobile Allowance	Health Insurance	Health Insurance
49	Misc.	3rd Party Liab.	3rd Party Liab.
50	LaonAdj	Bank Charges	Bank Charges
51	Gross Total	Mobile Allowance	Mobile Allowance
52	Service Charges	Misc.	Misc.
53	General Sales Tax	Gross Total	Gross Total
54	Billing Amount	Service Charges	Service Charges
55	Refund To Client	General Sales Tax	General Sales Tax
56	I.Tax	Billing Amount	Billing Amount
57	Professional Tax	I.Tax	I.Tax
58	EOBI Emp Con	Loan Ded.	Loan Ded.
59	Net Salary	Professional Tax	Professional Tax
60	EOBI Employee Arrears	EOBI Emp Con	EOBI Emp Con
61		EOBI Employee Arrears	EOBI Employee Arrears
62		Net Salary	Net Salary

6. Desired columns headers are of Feb Month (PRB DS)

7. Column Header should be dynamic, if any new columns gets added in the month of March it automatically displayed accordingly.

8. Link of Files are (https://drive.google.com/drive/folders/16lMbD6T_DsyXE3UzYGWVuf_EwxLcRzDa?usp=sharing)

Please suggest the query in power bi so that i am able to perform the task asap

Regards,

Saad Hanif

[PwerQueryKees]

EDIT: I moved the data taken from the top rows of each sheet to beginning ow the table.

I love it when people actually include useable and downloadable data!

So I took it and here is my result (more columns to the right, but you get the picture)

All done in one query, just create a blank query and paste and adapt...

let
    Source = Folder.Files("C:\Users\... your path to the folder containing your excel files here ..."),
    #"Keep Content and Name Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Added Excel WorkBook as Column" = Table.AddColumn(#"Keep Content and Name Columns", "Excel", each Excel.Workbook([Content])),
    #"Expanded Excel Workbook" = Table.ExpandTableColumn(#"Added Excel WorkBook as Column", "Excel", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Excel.Name", "Excel.Data", "Excel.Item", "Excel.Kind", "Excel.Hidden"}),
    #"Filtered Work Sheets only" = Table.SelectRows(#"Expanded Excel Workbook", each [Excel.Kind] = "Sheet"),
    #"Removed Name and Data Columns" = Table.SelectColumns(#"Filtered Work Sheets only",{"Name", "Excel.Name", "Excel.Data"}),
    #"Added Header Info as List" = Table.AddColumn(#"Removed Name and Data Columns", "Header", each List.Range([Excel.Data][Column1],1,6)),
    #"Moved Header to Front" = Table.ReorderColumns(#"Added Header Info as List",{"Name", "Excel.Name", "Header", "Excel.Data"}),
    #"Convert Header Info to Text" = Table.TransformColumns(#"Moved Header to Front", {"Header", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Turn Header Info into Columns" = Table.SplitColumn(#"Convert Header Info to Text", "Header", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Header.1", "Header.2", "Header.3", "Header.4"}),
    #"Remove Header Rows and Promote Headers" = Table.TransformColumns(#"Turn Header Info into Columns",{{"Excel.Data", each Table.PromoteHeaders(Table.Skip(_,8), [PromoteAllScalars=true])}}),
    #"Expanded Excel.Data" = Table.ExpandTableColumn(#"Remove Header Rows and Promote Headers", "Excel.Data", {"S.No.", "Employee Index", "Employee Id", "Employee No", "Employee Name", "Designation", "DOJ", "DOL", "Unit", "Department", "Sub Department", "Client Branch", "Division", "Region", "City", "Province", "Territory", "Location", "Remarks", "Salary Per Month", "Current Salary", "MEAL ALLOW", "Add / Del", "OT Hours", "OT Rate", "OT Amount", "Travel Allowance", "Travel Exp.", "Sp.OT Amount", "Working Days", "Extra Days", "Extra Days Amount", "Ex-Gratia", "Other Arrears", "Hardship Allowance", "Other Exp", "Absent Days", "Absent Days Amount", "EOBI", "EOBI Employer Arrears", "Edu. CESS", "ESSI", "ESSI Arrears", "Life Ins.", "Health Insurance", "3rd Party Liab.", "Bank Charges", "Mobile Allowance", "Misc.", "LaonAdj", "Gross Total", "Service Charges", "General Sales Tax", "Billing Amount", "Refund To Client", "I.Tax", "Professional Tax", "EOBI Emp Con", "Net Salary", "EOBI Employee Arrears", "Veh Rep/Maint", "Notice Salary", "Loan Ded."}, {"S.No.", "Employee Index", "Employee Id", "Employee No", "Employee Name", "Designation", "DOJ", "DOL", "Unit", "Department", "Sub Department", "Client Branch", "Division", "Region", "City", "Province", "Territory", "Location", "Remarks", "Salary Per Month", "Current Salary", "MEAL ALLOW", "Add / Del", "OT Hours", "OT Rate", "OT Amount", "Travel Allowance", "Travel Exp.", "Sp.OT Amount", "Working Days", "Extra Days", "Extra Days Amount", "Ex-Gratia", "Other Arrears", "Hardship Allowance", "Other Exp", "Absent Days", "Absent Days Amount", "EOBI", "EOBI Employer Arrears", "Edu. CESS", "ESSI", "ESSI Arrears", "Life Ins.", "Health Insurance", "3rd Party Liab.", "Bank Charges", "Mobile Allowance", "Misc.", "LaonAdj", "Gross Total", "Service Charges", "General Sales Tax", "Billing Amount", "Refund To Client", "I.Tax", "Professional Tax", "EOBI Emp Con", "Net Salary", "EOBI Employee Arrears", "Veh Rep/Maint", "Notice Salary", "Loan Ded."})
in
    #"Expanded Excel.Data"

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[lbendlin]

let
    Source = Folder.Files("C:\Users\xxx\Downloads"),
    ReadSheet = (xsheet)=> Table.PromoteHeaders(Table.Skip(xsheet,8), [PromoteAllScalars=true]),
    ReadFile = (xfile)=> let 
        #"Imported Excel Workbook" = Excel.Workbook(xfile),
        #"Filtered Rows1" = Table.SelectRows(#"Imported Excel Workbook", each ([Kind] = "Sheet")),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each ReadSheet([Data])),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "Sheet Name"}}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns", "Custom", Table.ColumnNames(#"Renamed Columns"[Custom]{0}))
    in
        #"Expanded Custom",      
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Feb 2024.xlsx" or [Name] = "Jan 2024.xlsx")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each ReadFile([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "File Name"}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns", "Custom", Table.ColumnNames(#"Renamed Columns"[Custom]{0}))
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[Saad_Hanif456]

@lbendlin, brother it is not working in my scenario,

In the third column there is error in Excel Column (why is that so?) + what if the Excel Sheet of the data is like below in of the sheets:

I have updated the files of Excel in this link. https://drive.google.com/drive/folders/16lMbD6T_DsyXE3UzYGWVuf_EwxLcRzDa?usp=drive_link 

Regard,
Saad Hanif

[lbendlin]

This is not a usable input format.  Find a way to avoid that.

[Saad_Hanif456]

Dear @lbendlin ,

The sheet is received in this format, else i have to do manual, work removing unnecessary rows. Please find out a way for it.

Regards,

Saad Hanif