Forum Discussion

MORDax's avatar
MORDax
Frequent Visitor
4 years ago
Solved

Make row values as Header


This is the type of data I have but I want it to show like the following: 

 


How do I acheive this?

 

Need Help
Show and Tell
  • danextian's avatar
    danextian
    4 years ago

    Hi MORDax ,

    You can use the Table.Pivot function in Power Query to achieve what you want. Here's sample M Script that shows this capability.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRCkktLklNATIMDPWByMjAyEgpVgcmG5Wfh1syIDE5G7usIYrBRlhk4QZjk0QYjC5rhNdgI3wGG+EyOBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, #"Col C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col A", Int64.Type}, {"Col B", type text}, {"Col C", type date}}),
    Custom1 = Table.Pivot(#"Changed Type", {"Tested", "Zoned", "Packed"}, "Col B", "Col C")
in
    Custom1

     

6 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Hi MORDax ,

    You can use the Table.Pivot function in Power Query to achieve what you want. Here's sample M Script that shows this capability.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRCkktLklNATIMDPWByMjAyEgpVgcmG5Wfh1syIDE5G7usIYrBRlhk4QZjk0QYjC5rhNdgI3wGG+EyOBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, #"Col C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col A", Int64.Type}, {"Col B", type text}, {"Col C", type date}}),
    Custom1 = Table.Pivot(#"Changed Type", {"Tested", "Zoned", "Packed"}, "Col B", "Col C")
in
    Custom1

     

    • MORDax's avatar
      MORDax
      Frequent Visitor
      4 years ago

      For me, it is showing up as this.

      I selected the second column and clicked Pivot column and selected Col C (original table) as Values Column (Don't Aggregate) but it is showing these null values and not appearing in one row. Please advise

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    In the Query Editor, click on any cell in the second column and click on Transform > Pivot.  The rest of it should be easy for your to figure out.

    • MORDax's avatar
      MORDax
      Frequent Visitor
      4 years ago



      It is coming up as this, not in one line 😕

  • mahenkj2's avatar
    mahenkj2
    Solution Sage
    4 years ago

    Hi MORDax ,

    It should be easy to implement a pivot.

    Better that you paste the data as table instead of image, so we can just exactly copy and paste and test. If I try with manually creating data, its working fine.