Forum Discussion

wherdzik's avatar
wherdzik
Icon for Helper III rankHelper III
2 years ago
Solved

Pivoting Two Columns, Is it Possible?

I want to Pivot Item Types but for ItemFTE and ProratedSalaryAllocation.  I can only do one of those columns but not both.

Is that even possible?

 

 

This is the only way I am able to do it, It wont let me bring in the ProratedSalaryAllocation colum.  

 

 

I want the end result to look something like this.

 

 

Thanks for your help.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi wherdzik ,

    Consider grouping and then unpivot column before transposing.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JzMtMTsxR0lEyBGJjA1NDS6VYHQwJQwMzQ3NzdBkDPTMgaWRsaIlDysLc2MwAhy5MCZBFpiYWZqZYXWBpZmxhhmmWOcgsS3NDiGn+JRmpRQq6Cp55JalFeVAlBqYgNYZGRriVgGwwMTIxAasISi1OTSxKzgAqcq3AaQ4uVQijYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ItemType = _t, ItemFTE = _t, ProratedSalaryAllocation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemType", type text}, {"ItemFTE", type number}, {"ProratedSalaryAllocation", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ItemType"}, {{"ItemFTE", each List.Sum([ItemFTE]), type nullable number}, {"ProratedSalaryAllocation", each List.Sum([ProratedSalaryAllocation]), type nullable number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"ItemType"}, "Attribute", "Value"),
    #"Transposed Table" = Table.Transpose(#"Unpivoted Columns")
in
    #"Transposed Table"

    And the output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

2 Replies

  • spinfuzer's avatar
    spinfuzer
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Merge the Columns by delimiter and then Pivot the Merged Column and Do Not Aggregate.  Split by Delimiter after the Pivot.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi wherdzik ,

    Consider grouping and then unpivot column before transposing.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JzMtMTsxR0lEyBGJjA1NDS6VYHQwJQwMzQ3NzdBkDPTMgaWRsaIlDysLc2MwAhy5MCZBFpiYWZqZYXWBpZmxhhmmWOcgsS3NDiGn+JRmpRQq6Cp55JalFeVAlBqYgNYZGRriVgGwwMTIxAasISi1OTSxKzgAqcq3AaQ4uVQijYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ItemType = _t, ItemFTE = _t, ProratedSalaryAllocation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemType", type text}, {"ItemFTE", type number}, {"ProratedSalaryAllocation", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ItemType"}, {{"ItemFTE", each List.Sum([ItemFTE]), type nullable number}, {"ProratedSalaryAllocation", each List.Sum([ProratedSalaryAllocation]), type nullable number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"ItemType"}, "Attribute", "Value"),
    #"Transposed Table" = Table.Transpose(#"Unpivoted Columns")
in
    #"Transposed Table"

    And the output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group