Forum Discussion
Excel Power query - Unpivot columns
- 1 year ago
consider this table and follow this process
right click on the column 1 and pick unpivot other columns to reach
then use the next fromula to split every three rows of table
= Table.Split(#"Unpivoted Other Columns",3)
in the next step use list.transform to reashape each table
= List.Transform(Custom1,each Table.ToRows(Table.Pivot(_,List.Distinct([Attribute]),"Attribute","Value")){0})resulting a list including sublist which reperesent arows of table, so use the next formula to reach the result
= Table.FromRows(Custom2)
the whole code is
let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"column1", type text}, {"PDT 1", type text}, {"CODE", Int64.Type}, {"Qty", Int64.Type}, {"PDT 2", type text}, {"CODE2", Int64.Type}, {"Qty3", Int64.Type}, {"PDT 3", type text}, {"CODE4", Int64.Type}, {"Qty5", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"column1"}, "Attribute", "Value"), Custom1 = Table.Split(#"Unpivoted Other Columns",3), Custom2 = List.Transform(Custom1,each Table.ToRows(Table.Pivot(_,List.Distinct([Attribute]),"Attribute","Value")){0}), Custom3 = Table.FromRows(Custom2) in Custom3
consider this table and follow this process
right click on the column 1 and pick unpivot other columns to reach
then use the next fromula to split every three rows of table
= Table.Split(#"Unpivoted Other Columns",3)
in the next step use list.transform to reashape each table
= List.Transform(Custom1,each Table.ToRows(Table.Pivot(_,List.Distinct([Attribute]),"Attribute","Value")){0})
resulting a list including sublist which reperesent arows of table, so use the next formula to reach the result
= Table.FromRows(Custom2)
the whole code is
let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"column1", type text}, {"PDT 1", type text}, {"CODE", Int64.Type}, {"Qty", Int64.Type}, {"PDT 2", type text}, {"CODE2", Int64.Type}, {"Qty3", Int64.Type}, {"PDT 3", type text}, {"CODE4", Int64.Type}, {"Qty5", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"column1"}, "Attribute", "Value"),
Custom1 = Table.Split(#"Unpivoted Other Columns",3),
Custom2 = List.Transform(Custom1,each Table.ToRows(Table.Pivot(_,List.Distinct([Attribute]),"Attribute","Value")){0}),
Custom3 = Table.FromRows(Custom2)
in
Custom3