Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Convert rows into columns

Hi All, wondering if anyone can help??

I have this set of date in this format 

and what i want it to look like is this

Almost like an unpivot with the Step being the header, but not with values.

 

Any ideas anyone?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Here is my sample data:

     


    Please try this M function, just put all of the M function into the Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnFV0lEy1DfUNzIwMgYyg0tSCxQMgYziksSiEqVYHfKVGAEZ+UUpqUVgJe5OOExJzkgswa0CZEhBUX5KaTLRNmFVAmKk5qUQbxFWRVBTFOBuJmgXWIEx3sAD24RTCZpd+I3CqgRLPJBrG5aAJsM2rEGNrgh7UGNTlZiemgdUEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Date = _t, Step = _t, Context = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Date", type date}, {"Step", type text}, {"Context", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Step]), "Step", "Context")
in
    #"Pivoted Column"

     And the final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Anonymous,

     

    • for future requests, provide sample data as table so we can copy/paste
    • this video should help you
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks but this doesnt work with the additional columns for country and date 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    If you want us to help you, provide sample data in proper format.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Here is my sample data:

     


    Please try this M function, just put all of the M function into the Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnFV0lEy1DfUNzIwMgYyg0tSCxQMgYziksSiEqVYHfKVGAEZ+UUpqUVgJe5OOExJzkgswa0CZEhBUX5KaTLRNmFVAmKk5qUQbxFWRVBTFOBuJmgXWIEx3sAD24RTCZpd+I3CqgRLPJBrG5aAJsM2rEGNrgh7UGNTlZiemgdUEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Date = _t, Step = _t, Context = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Date", type date}, {"Step", type text}, {"Context", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Step]), "Step", "Context")
in
    #"Pivoted Column"

     And the final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.