Dynamic table creation

[davidsch]

I need to convert a 16-column horizontal table to a 3-column vertical table. I would prefer to do this action via functions and not VBA.

• Each row in the current table represents one product. Columns represent product attributes.
• In the new table, each row should represent a product/attribute pair.
• I will incorporate the solution into a template where the number of products in the horizontal table can vary significantly.

Any thoughts on how to solve this are appreciated.

CURRENT TABLE STRUCTURE
Product Code	Cost	Pass Through	Attribute 3	Attribute 4	...Attribute 15
ABCD1
ABCD2
…
DESIRED TABLE STRUCTURE
Product Code	Attribute	Value
ABCD1	Cost
ABCD1	Pass Through
ABCD1	Attribute 3
ABCD1	Attribute 4
ABCD1	...Attribute 15
ABCD2	Cost
ABCD2	Pass Through
ABCD2	Attribute 3
ABCD2	Attribute 4
ABCD2	...Attribute 15

​

 

[AlanSidman]

unpivot your data with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Product Code"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"