Power Query from Folder - BUT not consolidating the top rows

Hello,
I am new to the forum and really a beginner in Excel.

I am trying to combine into 1 Excel Sheet about 20 files that are in the same folder (see file example attached).
I am using Power Query.
All the files have the same structure and I may add other files with the same structure to the folder other the coming months.
My challenge is that I want to consolidate the rows only starting from row 6 (data) and re-use Header from row 5.

Can you help me?

Thx,
Mikaël

What you'd do is, perform transformation using sample file.

Steps:
1. Combine files from folder using Get Data -> From File -> From Folder. Choose Combine & Edit option on window.
2. Now in the editor, select "Transform Sample File from..."
3. Choose column 2 and filter out null (Remove empty).
4. Promote first row to header.

See below for sample M code for "Transform Sample File from ...". Adjust column data type as needed.

let
    Source = Excel.Workbook(#"Sample File Parameter1", null, true),
    #"Hero Offer Summary_Sheet" = Source{[Item="Hero Offer Summary",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Hero Offer Summary_Sheet", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [Column2] <> null and [Column2] <> ""),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Offer", type text}, {"Country", type text}, {"Local Launch Manager", Int64.Type}, {"Original SELL Date (@ DO)", type any}, {"EFFECTIVE SELL Date (@ SELL)", type any}, {"Original Announcement Date (@ DO)", type any}, {"EFFECTIVE Announcement Date (@ SELL)", type any}, {"Launch Budget - M€", type any}, {"3 Years Ambitions - M€", type any}, {"Comments", type any}})
in
    #"Changed Type"

Make sure that you use query from folder. And use Combine & Edit.
Then make sure you apply transformation to auto-generated "Transform Sample...." query, and NOT the result query (combined).

Transformation applied to sample will propagate to each file that has same file/data structure and will eliminate duplicate headers.

I'm not familiar with BoxSync folder, but if it's mapped to local drive. PQ should not have an issue.

If you need more specific help in transformation, I'd recommend you zip some sample files with appropriate desensitized data (but with same data type).

To upload file, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

Ok, so looking at your attempt. You did not apply steps at correct stage.

All transformation steps, excluding merge and changing data type should occur in "Transform Sample File from Hero Files" query, and not in "Hero Files" query.
0.JPG

Data type change should be applied at last stage of "Hero Files".
See attached sample (In the editor, go to "Data source setting and change source to your actual folder).
1.JPG

I'm not certain how you want to use "Country Table.xlsx". Is it simple left join using Country as key (i.e. VLookup)?

Edit: I won't have access to PC for rest of the day, but should have time to look at it tomorrow.

I was thinking to add 2 columns in between Country & Launch Manager columns within Consolidation Test File and insert Vlook-up formulas but I am not sure this is the best way to do it. Do I do it in the consolidated sheet or do I implement a formula in the query? Your thought?

Your previous file is working very well and this is what I need but I am not sure I understood your explanation to remove headers duplicates and keep formatting. I compared your & my file and I don't see the differences in the Query steps...