Forum Discussion

kimmal's avatar
kimmal
Regular Visitor
8 years ago
Solved

Help Unpivoting data

I have data which I need to unpivot into 2 columns, one for the week attribute and one for the value.  All the data in on the same row.  The issue is I have multiple columns for data with the associa...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi kimmal,

     

    With help from MarcelBeug, this code will perfectly solve your probelm - there will no longer be a limitation of upto 10 columns

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO", type text}, {"Date_Received", type datetime}, {"ProcessDate1", type datetime}, {"Value1", Int64.Type}, {"ProcessDate2", type datetime}, {"Value2", Int64.Type}, {"ProcessDate3", type datetime}, {"Value3", Int64.Type}, {"ProcessDate4", type datetime}, {"Value4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"WO", "Date_Received"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Combine(Text.SplitAny([Attribute],"0123456789"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.End([Attribute],Text.Length([Attribute])-Text.Length([Custom]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.1"})
in
    #"Removed Columns1"

    Hope this helps.

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

After unpivot, the trick is to add an Index column (starting with 0) and integer-divide that column by the number of different items (i.c. 2). Then you can adjust the attribute column so - in this case - you have "Date" and "Value" entries.

Now you can pivot back (with advanced option "Don't Summarize"), remove the Index column and adjust data types.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO", type text}, {"Date_Received", type date}, {"ProcessDate1", type date}, {"Value1", Int64.Type}, {"ProcessDate2", type date}, {"Value2", Int64.Type}, {"ProcessDate3", type date}, {"Value3", Int64.Type}, {"ProcessDate4", type date}, {"Value4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"WO", "Date_Received"}, "Attribute", "Value"),
    #"Transformed Attribute" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each if Text.Contains(_,"Date") then "Date" else "Value"}}),
    #"Added Index" = Table.AddIndexColumn(#"Transformed Attribute", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type1"