Transpose Excel Table Horizontally, but also multiply the number of rows for the first column?

Question

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.

Answer 1

You can do this easily in Power Query, available in Windows Excel 2010+, and Excel 365.

• Select some single cell in your source table
• Data => Get Data => from within sheet
• In the PQ UI, select the Zip column
• Transform => Unpivot => Unpivot other columns
• Delete the Values column
• Rename the Attribute Column => Color
• Home => Close & Load

Here 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"