Transpose only selected rows into columns (for over 190 columns)

Someone is using Essbase 😁

Start by Merging the Product ID and Departments by a delimiter that is not in your data. I picked pipe (|).

Transpose the table. Promote headers and rename the Entity through Product columns.

Now unpivot the other columns (the data details).

Split the Product ID and Department column on the pipe

Rename the split columns.

Below shows how it works.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"|", "Entity"}, {"|_1", "Activity"}, {"|_2", "Year"}, {"|_3", "Month"}, {"|_4", "Scenario"}, {"|_5", "Version"}, {"|_6", "Product"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Product", "Version", "Scenario", "Month", "Year", "Activity", "Entity"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "DeptID"}, {"Attribute.2", "Account"}, {"Value", "Amount"}})
in
    #"Renamed Columns1"

Hope this helps!