Transform multi row header from Excel Table in Power Query

The trick is to do it in a few pieces and then stick them together. For example, I'd do this in three pieces.

 

#1 Company:

let
    Source = [#"Company ID" = Table1{0}[Column4], #"Company" = Table1{0}[Column5]],
    #"Converted to Table" = Record.ToTable(Source),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value")
in
    #"Pivoted Column"

 

#2 Date+IDs:

let
    Source = Table1,
    #"Kept First Rows" = Table.FirstN(Source,3),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Other Columns",{"Column3", "Column4", "Column5"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1", "Column2"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type datetime}, {"Internal ID", Int64.Type}, {"Substitute ID", Int64.Type}})
in
    #"Changed Type"

 

#3 Delivery + #1 + #2:

let
    Source = Table1,
    /*Extract Delivery data*/
    #"Removed Top Rows" = Table.Skip(Source,5),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Delivery ID] <> null)),
    /*Add in Company table*/
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Company),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Company ID", "Company"}, {"Company ID", "Company"}),
    /*Add in Date+IDs table*/
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each #"Date+IDs"),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Date", "Internal ID", "Substitute ID"}, {"Date", "Internal ID", "Substitute ID"}),
    /*Cleanup*/
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom1",{"Date", "Internal ID", "Substitute ID", "Company ID", "Company", "Delivery ID", "Order Nr", "Truck", "Article", "Article Desc", "Content", "Pallet Desc", "Sub Article", "Origin", "Category", "C/C"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}, {"Internal ID", Int64.Type}, {"Substitute ID", Int64.Type}, {"Company ID", Int64.Type}, {"Company", type text}, {"Delivery ID", Int64.Type}, {"Order Nr", type text}, {"Truck", Int64.Type}, {"Article", Int64.Type}, {"Article Desc", type text}, {"Content", type text}, {"Pallet Desc", Int64.Type}, {"Sub Article", Int64.Type}, {"Origin", type text}, {"Category", type text}, {"C/C", Int64.Type}})
in
    #"Changed Type"

 

See attached: