Forum Discussion
Complex Transpose and Combine
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
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
- Paste the data in blue table
- 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"Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
 
