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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Try this M language solution

 

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"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, true), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

The limitation of this method is that it will only work with a dataset which has upto 10 columns of ProcessDate per WO.

 

Hope this helps.

kandre123's avatar
kandre123
Advocate I
6 years ago

Ashish_Mathur how do this code work for 300 columns?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Describe the question, share some data and show the expected result.