Source data has header titles on different rows

[Yunus_Babu]

My issue is the following:
Imagine you have 5 files with each file having a worksheet with the the same report. But the reports are not structured the same way. File 1 has the report title starting on row 10, file 2 row title is starting on row 9, file 3 on row 15, file 4 on row 8 and file 5 on row 13. For each file there is data above the rows which I want to use as the row header (someone over the years had the idea of using the blank space above the header row to add various summaries so these can't be removed). Basically, each file is poorly laid out and to make matter worse, the columns are not in the same order for each file.

My request is following:
How do I instruct power query to make the rows I deem as the header from each of the five files? What do I need to do to make this happen? For the columns, I believe you need to use M code and the following video (I think) has the answer to the problem of inconsistent column layout but I am snookered with the inconsistencies in the rows and can't find a solution.

Column solution is following:
https://www.youtube.com/watch?v=xulaPRyp5EE

Can anyone help me solve the inconsistent row issue?

Thank you.

[mussaenda]

Hi @Yunus_Babu ,

If you are using a folder to get the different files, you can do it in the Transform sample file to remove the unnecessary rows before loading all the files

[Omid_Motamedise]

See this video

https://www.youtube.com/watch?v=pofthD7-YuQ

[CaptOdyssey]

Hi, are the rows before your header blank? If yes, you can remove the blank rows, transpose the table, merge the two columns you want to be the header, transpose the table again and finally promote the first row to be headers.

[Yunus_Babu]

Hello Omid and CaptOdyssey,

Apologies, but I don't think I explained myself properly.

My issues is that the 5 source files have the report/header titles starting on different rows in each file. I want to use Power Query in Excel to combine the 5 files together with the header titles being the first row. As the 5 source files have the header/report titles on different row numbers, I am unsure how to do this. Also, the 5 source files have other data above the row which is the report/header titles which adds to the complication.

I hope I've that more clear.