Forum Discussion

NPJ's avatar
NPJ
New Member
1 year ago
Solved

Excel Power query - Unpivot columns

Hello, I am trying to unpivot from a database some columns from one file. I made up an example, where I need to change the format from the first table into the format of the second table below The ...
  • Omid_Motamedise's avatar
    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