Transformation conundrum!

LouStagner,

The no-code steps from Vvelarde have a smarter merge than mine.  Here's my query again (inc. comments) to include his approach which uses only query/table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//Split out the first four rows as the FIRST table
    #"Kept First Rows" = Table.FirstN(Source,4),
//Just keep the first (identical) column
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1"}),
//Turn the column into a row
    #"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
//Add the key column for later join
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Column5", each 1),
//Rename columns
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "Period"}, {"Column2", "Year"}, {"Column3", "Division #"}, {"Column4", "Division Name"}}),
// Now create the SECOND table by removing the first four rows from Source
    #"Removed Top Rows" = Table.Skip(Source,4),
//Promote the column headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
//Add the key column
    #"Added Custom1" = Table.AddColumn(#"Promoted Headers", "Column5", each 1),
//Join the two tables on the newly added key column
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"Column5"},#"Renamed Columns",{"Column5"},"NewColumn",JoinKind.FullOuter),
//Expand out the contents of the joined columns
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Period", "Year", "Division #", "Division Name"}, {"Period", "Year", "Division #", "Division Name"}),
//Tidy by removing the join column
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Column5"})
in
    #"Removed Columns"

This worked perfectly victor! Thank you.

Thanks to everyone else for their input.

The conundrum however continues as there is a folder full of files in this format that I need to mash up together.