Unpivot

Hey, rinson

by nature of what you wanna do, you will always produce duplicates, however, it's possible to unpivot as you want, follow this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY+7CsJAEEX/ZesU2XkIlgG10UJ8VCFFhICFmBT5f7J7hF2buzuXMwem70MX2xiacH3P3ym9z3uXMkqbU0kjPae0ZCRhlMYTMzTIJI2XcVnnJdvOBRRkgky8rioyhTGafZVpGh/j6zOt6XM4FlCRKTL1umrIDMZpolSb/d15uhXQkBky87rqyBxm95Nx57AB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Country = _t, #"Jan-2024" = _t, #"Feb-2024" = _t, #"Mar-2024" = _t, #"Apr-2024" = _t, #"Jan-2025" = _t, #"Feb-2025" = _t, #"Mar-2025" = _t, #"Current Month Sales" = _t, YTD = _t]),
    columnsToKeep= List.Select( Table.ColumnNames( Source ), each not Text.Contains(_, "2024" ) ),
    unpivot = Table.UnpivotOtherColumns(Source, columnsToKeep, "YearMonth", "Sales" )
in
    unpivot

Copy paste it into Blank Query to see example.

If you want a better approach, I recommend keeping only the stuff you want to pivot, like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY+7CsJAEEX/ZesU2XkIlgG10UJ8VCFFhICFmBT5f7J7hF2buzuXMwem70MX2xiacH3P3ym9z3uXMkqbU0kjPae0ZCRhlMYTMzTIJI2XcVnnJdvOBRRkgky8rioyhTGafZVpGh/j6zOt6XM4FlCRKTL1umrIDMZpolSb/d15uhXQkBky87rqyBxm95Nx57AB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Country = _t, #"Jan-2024" = _t, #"Feb-2024" = _t, #"Mar-2024" = _t, #"Apr-2024" = _t, #"Jan-2025" = _t, #"Feb-2025" = _t, #"Mar-2025" = _t, #"Current Month Sales" = _t, YTD = _t]),
    columnsToKeepStatic= {"YTD", "Current Month Sales", "Country", "Type", "Item"},
    columnsToKeep2024 = List.Select( Table.ColumnNames( Source ), each  Text.Contains(_, "2024" ) ),
    filterRightColumns = Table.SelectColumns( Source, columnsToKeepStatic & columnsToKeep2024 ),
    unpivot = Table.UnpivotOtherColumns(filterRightColumns, columnsToKeepStatic, "YearMonth", "Sales" )
in
    unpivot