Power query to merge workbook base on header names and rename

[12Rev79]

Dear Experts,

Is a power query can merge workbooks base on header names like
Column B1 Unit
Column C1 Service
Column D1 Line
Column E1 WBS
Column F1 Suffix
Column G1 Diameter
Column L1 Joint
Column N1 Type
Column Z1 Fit Up
Column AB1 Welding date

I also have another issue to ask is power query can change header name like in header name "Welding date" for sometimes the user write as "Weld date"
and sometimes for header name "Service" the user by typographic error write as "Servce".

Note :
A. my workbooks are save in my desktop folder "01 prodn" with a subfolder as below
1 Michael
2 Robert
3 Abdul
4 Ahmed

B. my workbooks have different sheetnames like "Sheet1" / "WeldingReport" / "Welding" and etc...

Please maybe someone is kind to help me figure out the code.

Thank you in advance,

12Rev79

 

[Kaper]

My honest proposition goes just opposite direction.
Think about a way to enforce consistent input data. May be some motivation for team, may be some challenge, may be just properly prepared templates, may be forms to gather data (with rules, dropdown lists, ...), etc.
Otherwise you will soon have data starting in row 3, sequence of columns changed, missing data in rows, two reports in one workbook, etc.
You are not facing reading reports from welders of Titanic hull, but the reports are prepared now by people working for you, so you can expect from them not only work done but also reporting as required.