Complex Transpose and Combine

%3CLINGO-SUB%20id%3D%22lingo-sub-1215204%22%20slang%3D%22en-US%22%3EComplex%20Transpose%20and%20Combine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215204%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20CSV%20where%20the%20fields%20are%20all%20located%20in%20one%20column%20per%20rows%20repeated%20unique%20values%20for%20each%20field.%3C%2FP%3E%3CP%3EI.E.%3C%2FP%3E%3CP%3EColumn1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Column2%3C%2FP%3E%3CP%3EDate%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%2F1%2F1900%3C%2FP%3E%3CP%3EColor%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Blue%3C%2FP%3E%3CP%3ETime%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%204%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDate%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%2F2%2F1900%3C%2FP%3E%3CP%3EColor%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Red%3C%2FP%3E%3CP%3ETime%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%203%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20them%20to%20be%20consolidated%20and%20transposed%20to%20the%20following%3A%3C%2FP%3E%3CP%3EDate%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Color%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Time%3C%2FP%3E%3CP%3E1%2F1%2F1900%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Blue%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%204%3A00%3C%2FP%3E%3CP%3E1%2F2%2F1900%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Red%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%203%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20programmatic%20way%20to%20accomplish%20this%20in%20excel%20easily%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1215204%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1215770%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20Transpose%20and%20Combine%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215770%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F576444%22%20target%3D%22_blank%22%3E%40JWPhnx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20unstack%20your%20data%20please%20find%20below%20solution%20step%20using%20Power%20Query%2C%20considering%20your%20ExcelTable%20name%20is%20Table1%2C%20you%20can%20do%20the%20following.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOption%201%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EYou%20can%20copy%20the%20below%20code%20and%20paste%20in%20Power%20Query%20Advance%20Editor%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOption%202%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EPaste%20the%20data%20in%20blue%20table%3C%2FLI%3E%3CLI%3ESelect%20the%20Green%20Excel%20Table%20%26gt%3B%20right%20click%20%26gt%3B%20refresh.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EThis%20will%20work%20like%20magic%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-big-eyes%22%20title%3D%22%3Agrinning_face_with_big_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(Source%2C%7B%7B%22Column1%22%2C%20type%20text%7D%2C%20%7B%22Column2%22%2C%20type%20any%7D%7D)%2C%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(%23%22Changed%20Type%22%2C%20each%20(%5BColumn1%5D%20%26lt%3B%26gt%3B%20null))%2C%0A%20%20%20%20%23%22Added%20Index%22%20%3D%20Table.AddIndexColumn(%23%22Filtered%20Rows%22%2C%20%22LineNo%22%2C%201%2C%201)%2C%0A%20%20%20%20%23%22Added%20Conditional%20Column%22%20%3D%20Table.AddColumn(%23%22Added%20Index%22%2C%20%22FileNo%22%2C%20each%20if%20%5BColumn1%5D%20%3D%20%22Date%22%20then%20%5BLineNo%5D%20else%20null)%2C%0A%20%20%20%20%23%22Filled%20Down%22%20%3D%20Table.FillDown(%23%22Added%20Conditional%20Column%22%2C%7B%22FileNo%22%7D)%2C%0A%20%20%20%20%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(%23%22Filled%20Down%22%2C%7B%22LineNo%22%7D)%2C%0A%20%20%20%20%23%22Reordered%20Columns%22%20%3D%20Table.ReorderColumns(%23%22Removed%20Columns%22%2C%7B%22FileNo%22%2C%20%22Column1%22%2C%20%22Column2%22%7D)%2C%0A%20%20%20%20%23%22Pivoted%20Column%22%20%3D%20Table.Pivot(%23%22Reordered%20Columns%22%2C%20List.Distinct(%23%22Reordered%20Columns%22%5BColumn1%5D)%2C%20%22Column1%22%2C%20%22Column2%22)%2C%0A%20%20%20%20%23%22Changed%20Type1%22%20%3D%20Table.TransformColumnTypes(%23%22Pivoted%20Column%22%2C%7B%7B%22Date%22%2C%20type%20date%7D%2C%20%7B%22Color%22%2C%20type%20text%7D%2C%20%7B%22Time%22%2C%20type%20time%7D%7D)%2C%0A%20%20%20%20%23%22Removed%20Columns1%22%20%3D%20Table.RemoveColumns(%23%22Changed%20Type1%22%2C%7B%22FileNo%22%7D)%0Ain%0A%20%20%20%20%23%22Removed%20Columns1%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-03-07_17-15-09.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F175763iCDCEEC32631EFD96%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222020-03-07_17-15-09.png%22%20alt%3D%222020-03-07_17-15-09.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%20Answer%3C%2FSPAN%3E.%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have a CSV where the fields are all located in one column per rows repeated unique values for each field.

I.E.

Column1                   Column2

Date                               1/1/1900

Color                               Blue

Time                              4:00

 

Date                               1/2/1900

Color                             Red

Time                              3:00

 

I need them to be consolidated and transposed to the following:

Date              Color                Time

1/1/1900       Blue                  4:00

1/2/1900       Red                   3:00

 

Is there a programmatic way to accomplish this in excel easily?

1 Reply
Highlighted

HI @JWPhnx 

 

To unstack your data please find below solution step using Power Query, considering your ExcelTable name is Table1, you can do the following.

 

Option 1

You can copy the below code and paste in Power Query Advance Editor

 

Option 2

  1. Paste the data in blue table
  2. Select the Green Excel Table > right click > refresh.

This will work like magic  

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "LineNo", 1, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "FileNo", each if [Column1] = "Date" then [LineNo] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"FileNo"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"LineNo"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"FileNo", "Column1", "Column2"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Column1]), "Column1", "Column2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Color", type text}, {"Time", type time}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"FileNo"})
in
    #"Removed Columns1"

 

2020-03-07_17-15-09.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.