excel - Transform headers to cells and join

Question

Current data:

Desired outcome:

I considering writing a basic VBA script, to loop through and concatenate. However since this will be running through 44 columns 25k-130k rows. I would prefer a more efficient solution.

I believe this could perhaps be accomplished fairly efficiently with power query. Any assistance would be greatly appreciated.

question from:https://stackoverflow.com/questions/66057628/transform-headers-to-cells-and-join

Answer 1

Power Query is good here. Unpivot the non-Item columns and then aggregate with Text.Combine.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotColumns = Table.UnpivotOtherColumns(Source, {"Item"}, "Qualities", "Value"),
    GroupRows = Table.Group(UnpivotColumns, {"Item"}, {{"Qualities", each Text.Combine([Qualities], ","), type text}})
in
    GroupRows