How to I transform a table of data into 3 columns? In excel. I think its called stacking.

Question

How to I transform a table of data into 3 columns? In excel. I think its called stacking.

Anonymous

I have a table of data across 24 months.

Essentially as below

	Jim	Bob	Sarah	Jane
Jan-23	5	7	10	100
Feb-23	100	0	6	9
Mar-23	10	50	20	8
etc.

I would like the data to be displayed as

Month	Name	Number
Jan-23	Jim	5
Jan-23	Bob	7
Jan-23	Sarah	10

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Answer 1

Power Query is your friend in this case.

A very basic script, that unpivots the blue table ("Table1") to create the green one.

let

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

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Month"}, "Attribute", "Value"), 

#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}), 

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Month", type date}})

in

#"Changed Type"

Share via

How to I transform a table of data into 3 columns? In excel. I think its called stacking.

1 answer