Transformation conundrum!

LouStagner,

If the Tranpose/Unpivot/Transpose approach from GilbertQ doesn't pan out, you can try a mashup like below - split out the first four rows and transpose, then join back to the rest of the table and sort and fill down and filter out the nulls.  But this will be sensitive to data changes...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Kept First Rows" = Table.FirstN(Source,4),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Column5", each ""),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "Period"}, {"Column2", "Year"}, {"Column3", "Division #"}, {"Column4", "Division Name"}}),
    #"Removed Top Rows" = Table.Skip(Source,4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
    #"Merged Queries" = Table.NestedJoin(#"Promoted Headers",{"AccountNumber", "Period Budget", "Period Actual", "YTD Actual", "YTD Budget"},#"Renamed Columns",{"Period", "Year", "Division #", "Division Name", "Column5"},"NewColumn",JoinKind.FullOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Period", "Year", "Division #", "Division Name", "Column5"}, {"Period", "Year", "Division #", "Division Name", "Column5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Column5"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Period", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Period", "Year", "Division #", "Division Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [AccountNumber] <> null)
    
in
    #"Filtered Rows"

Thanks for your feedback,

I am still having no luch trying to make this work through a combination of trasnforms and pivot/unpivot.

There has got to be a way, but whatever that way is, I can't figure it out!