Transpose each table before expanding

Try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {
        {"Pivot",(t)=>
            let 
                #"Remove Index" = Table.RemoveColumns(t,"Index"),
                #"Unpivot" = Table.UnpivotOtherColumns(#"Remove Index",{},"Attribute","Value"),
                #"Col Names" = 
                    Table.RemoveColumns(
                        Table.TransformColumns(
                            Table.AddIndexColumn(#"Unpivot","New Cols",1,1,Int64.Type),
                        {"New Cols", each Number.ToText(_, "'Column'0")}),
                    {"Attribute"}),
                Pivot = Table.Pivot(#"Col Names",#"Col Names"[New Cols],"New Cols","Value")
            in 
                #"Pivot"}
    }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Columns", "Pivot", {"Column1", "Column2", "Column3", "Column4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Pivot",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}})
in
    #"Changed Type1"