Pivot Severals Columns

[marakud]

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 !

[AlexisOlson]

@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"

[serpiva64 · Accepted solution]

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!