Stack and Pivot?

Hi Anonymous

It is possible to transform the data into the output you want. Create a blank query and paste below codes into its Advanced editor to see the steps. Or download the attachment at the bottom.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdGtDsJAEEXhVyGra9g/XzozHt1UICoQbAmFEN4eBdnNEWuv+MQ98+zOr3V/XrdyOLrBTdvtfimfcSz7e31UywnL9FuWoUI8EA/EA/EtEoAEIAFIaJEIJAKJQGKLJCAJSAKSWiQDyUAykNwi1fOCForFenUELRSL9eoIWigW69URtFAs1qsjaKFYrFdH0EKx2L/O8gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"AllContent", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text, Column4=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Combine({[AllContent][Column2], [AllContent][Column3], [AllContent][Column4]})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllContent"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.