I'm unpivoting and transposing (input) in order to have (output) formatted for internal systems. Solution provided by @RonRosenfeld in

[Output]

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Index", Int64.Type}, {"Person", Int64.Type}, {"Dept", Int64.Type}, 
        {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, 
        {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, 
        {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, 
        {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}, 
        {"Time", type datetime}, {"User", type text}},"en-150"),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {"Index", "Person", "Dept", "Time", "User"}, "Month", "Sales"),

    mnthCol = Table.TransformColumns(#"Unpivoted Other Columns", {"MNTH", each 
        Date.ToText(Date.FromText("1-" & _ & DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),8),"yyyy")),"MM.yyyy"),type text}),
    #"Changed Type1" = Table.TransformColumnTypes(mnthCol,{{"MNTH", type date}}),

    FC = Table.AddColumn(#"Changed Type1", "Fiscal Month", each if Date.ToText(Date.Month([MNTH])) >=7 then Date.ToText(Date.Month([MNTH]))-6 else Date.ToText(Date.Month([MNTH])) 6),

    #"Reordered Columns" = Table.ReorderColumns(mnthCol,{"Index", "Person", "Dept", "Month", "Sales", "Time", "User"}),

    rename = Table.RenameColumns(#"Reordered Columns",{
        {"Time","STMP"},
        {"Dept","Depr"}
    })
in
   rename 

CodePudding user response：

Adjustment to the original solution is to change

mnthCol = Table.TransformColumns(#"Unpivoted Other Columns", {"Month", each 
    Date.ToText(Date.FromText("1-" & _ & "-2022"),"M.yyyy"),type text}),

to something along the lines of

   mnthCol = Table.TransformColumns(#"Unpivoted Other Columns", {"Month", each 
    if  Date.Month(Date.FromText("1-" & _ & "-1900"))<7 then 
    Date.ToText(Date.FromText("1-" & _ & "-2023"),"M.yyyy")
   else 
   Date.ToText(Date.FromText("1-" & _ & "-2022"),"M.yyyy")
    ,type text}),

but you'd have to set the 7 dynamically somehow to determine which months are in which year