Sorry if this has been asked before, but I was unable to find anything specific to this need, probably due to it being odd to phrase. Essentially I have a table like this that I would like to transform into the table below:
| zip | blue | green |
|---|---|---|
| 10000 | 1 | 2 |
| zip | color |
|---|---|
| 10000 | blue |
| 10000 | green |
Ideally, I would like to do this all purely in SQL or purely in Excel, but eventually I will want to transpose through R or Python once I get more familiar.
You can do this easily in Power Query, available in Windows Excel 2010+, and Excel 365.
Data => Get Data => from within sheetZip columnTransform => Unpivot => Unpivot other columnsValues columnAttribute Column => ColorHome => Close & LoadHere is M-Code that will do the same thing, with some changes so as not to have to hard-code other colors that you might have besides the two you show.
You would paste this into the Advanced Editor of PQ; change the Table name at the top. Then read the comments and explore the Applied Steps to better understand the algorithm.
let
//Change table name in next line to the actual name in your workbook
Source = Excel.CurrentWorkbook(){[Name="Table29"]}[Content],
//set data types
// Zip is text to retain leading zero's
// Others are all integers
#"Changed Type" = Table.TransformColumnTypes(Source,
{{"zip", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, Int64.Type})),
//Unpivot all columns except for the Zip column
// And name the "color" column as "Color"
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"zip"}, "Color", "Value"),
//Remove the value column since you do not show it in your result example
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"})
in
#"Removed Columns"
