Manually Assign Certain Rows as Column Headers

This is much more difficult than it should be because of how the file is laid out, but I get your users are comfortable with that structure. A true Excel Table would make this a non-issue.

 

Paste this code into a blank query in Power Query.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive - eHansalytics\Downloads\File2.xlsx"), null, true),
    #"MP Date_Sheet" = Source{[Item="MP Date",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"MP Date_Sheet",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type",5),
    #"Filled Down" = Table.FillDown(#"Kept First Rows",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36"}),
    #"New Header" = Table.LastN(#"Filled Down", 1),
    #"Data Rows" = Table.Skip(#"Changed Type", 5),
    #"New Table" = Table.Combine({#"New Header", #"Data Rows"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"New Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

You'll need to change the Source line to your file path on your PC.

Here is what I did:

1. I only did this on the MP table as an example.
2. I kept the top 5 rows as those were the header rows. I didn't do anything to make this dynamic to find the first data row so you might have to change this per tab.
3. I then filled down on all columns., then I removed all but the last row. This will be my new header row in a few steps
4. I kept the data rows, which is all but the top 5 rows. Note this points back to the #"Changed Type" step, not the previous step.
5. I combined the New Header row with the Data Rows. Now I have one nice table.
6. On the Home Ribbon, pressed Used First Rows as Headers.

You will have to tweak the M code in the formula bar for this to work. The UI buttons will not let you jump around in steps like I am doing here.