Forum Discussion

jkhan's avatar
jkhan
Helper III
3 years ago

Table Transpose in Visual

Hi All,

 

Require help to transpose the below table 

 

like below

 

The idea is need to make Machine1,Machine2,Machine3 as column and remaining as rows.

 

Thanks & Best Regards

Jamsher

Need Help

13 Replies

  • Bhu1singh's avatar
    Bhu1singh
    Frequent Visitor
    3 years ago

    Hi,
    You can transpose the table in Power Query,

    1. Go to power query Editor>> right click on Machin_number column and say unPivot other columns

    2. Then again select Machin_number column and in Tranformation menu select Pivot option.

    You should get the table in desired format.

     

    Thanks

    Bhuvan

    • jkhan's avatar
      jkhan
      Helper III
      3 years ago

      Hi Bhu1singh ,

       

      I did the steps as you mentioned. But getting below result. I can attached sample pbix file to download. 

       

       

      file download : CPU_PRODUCTION.pbix

       

      Thanks & Regards

      Jamsher

       

      • Bhu1singh's avatar
        Bhu1singh
        Frequent Visitor
        3 years ago

        Hi jkhan 

        I dont have access to your data, can you past below steps in a blank query (data source) in power query editor. This is what I am suggesting to implement.

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MzsjMSzVU0lECY31DfSMDIyMgMzGlWClWB64CJGRkZAxWYwRXU5yGrAYkawJWYYwwBUOFKViFCYqKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machine_Number = _t, Prod1 = _t, Prod2 = _t, Prod3 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine_Number", type text}, {"Prod1", Int64.Type}, {"Prod2", type date}, {"Prod3", type text}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Machine_Number"}, "Attribute", "Value"),
        #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Machine_Number", "Attribute"}, {{"Unique_Value", each List.Max([Value]), type any}}),
        #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Machine_Number]), "Machine_Number", "Unique_Value")
        in
        #"Pivoted Column"

         

        Thanks

        Bhuvan

  • jkhan's avatar
    jkhan
    Helper III
    3 years ago

    Dear All,

     

    Any hint please. 

     

    Thanks & Regards

    Jamsher

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      3 years ago

      So which date/time needs to be shown? Machine 1 also has several date/time values

      • jkhan's avatar
        jkhan
        Helper III
        3 years ago

        PaulDBrown ,

        We can create machine2_1 and machine2_2 colum it we have mutliple records for machine in same date 

         

        Thanks & Regards