Forum Discussion

mschroetel's avatar
mschroetel
New Member
8 years ago
Solved

Unpivot Multiple Coumns

I have a direct connection to a databased containing sales transaction data from a point of sale.   Each transaction can split the revenue between 6 profit centers.   In the transaction data there a...
  • ImkeF's avatar
    ImkeF
    8 years ago

    I've mocked up a sample with the solution here. Please paste into the advanced editor and follow the steps:  

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYkMDIJEExMYGSrE60UpOUHEjmLgJUDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, pcsplit_1 = _t, prctr_1 = _t, pcsplit_2 = _t, prctr_2 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    OptionalRemove = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    OptionalRemove
ImkeF's avatar
ImkeF
Community Champion
8 years ago

1) Check the columns that shall stay and choose "Unpivot other Columns"

2) Check (the new) colum "Attribute" and Split by delimiter "_"

3) Check the first of the resulting columns (that contain "pctr" and "pctsplit") and pivot again. 

  • mschroetel's avatar
    mschroetel
    New Member
    8 years ago

    ImkeF Thanks for the reply.  Can you clarify what you mean on the step 3?  Am I pivoting the first column "attribute.1" on the newly created "attribute.2" column?

    Thanks!

    • ImkeF's avatar
      ImkeF
      Community Champion
      8 years ago

      I've mocked up a sample with the solution here. Please paste into the advanced editor and follow the steps:  

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYkMDIJEExMYGSrE60UpOUHEjmLgJUDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, pcsplit_1 = _t, prctr_1 = _t, pcsplit_2 = _t, prctr_2 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    OptionalRemove = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    OptionalRemove