Forum Discussion

marakud's avatar
marakud
Frequent Visitor
3 years ago
Solved

Pivot Severals Columns

Hello everyone.

I am contacting you to ask for your help.

Indeed, I would like to perform the transformation below (see image) in power query.

 

 

Can you help me please

 

Thanks !

 

 

Need Help
  • serpiva64's avatar
    serpiva64
    3 years ago

    Hi,

    from this

    you can obtain this

    applying the steps of the attached file

     

    If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

2 Replies

  • serpiva64's avatar
    serpiva64
    Solution Sage
    3 years ago

    Hi,

    from this

    you can obtain this

    applying the steps of the attached file

     

    If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    serpiva64's solution looks like it works fine but I think I have a simpler approach.

     

    Unpivot the non-date columns, split the attribute column, and then pivot back.

     

    Here's a full sample query you can paste into the Advanced Editor of a new blank query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDSNzIwMlbSUTICYueMxBKFvPzMIiAbJJafWZyaWApkmCM4sTpAjUZoGpMzMlPzgLQJmiEWcDmwNmMc2kyR2JZgdj4QQTWZ4HGkGZJiHSVDAzRnmmLYB1Nqjuw7Q0MUY8FazdC0whVboDnBENVNsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Quantité 1" = _t, #"Désign 1" = _t, #"Quantité 2" = _t, #"Désign 2" = _t, #"Quantité 3" = _t, #"Désign 3" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Column]), "Column", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"