Forum Discussion
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 ! ...
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!
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"