convert columns into rows. Not Unpivot....I think....

Hi  Anonymous,

By my tests, both of the solutions from kamran_bsh and Stachu should be right.

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZBNC4JAEIb/yrLnFC09eDTJU0ZgYGAi0zqUsa6x7lr9+zJvK3SIOs3XMzMvb57TuOZIZzTi0J1dL6DFLKcSWAlVD4JhVXbQXDmWV9lekClb3JiJH7TjLNimJRx75O9qcjJVUjOlJXASS2hqcTKJSMseK7JEaP4+SlC9hFgkVYhDDNNdQsIgmJvgD9xx3NeDlVDyQdaDPx+ciVquG9FN1JaZldUVkpiDOKE1YiaVzX1n7wW2+926acnC80myBRMbGsd3dh+z4gk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [clashresults = _t, clashName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"clashresults", type text}, {"clashName", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"clashName"}, {{"results", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [results][clashresults]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"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", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15", "Custom.16", "Custom.17"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type text}, {"Custom.7", type text}, {"Custom.8", type text}, {"Custom.9", type text}, {"Custom.10", type text}, {"Custom.11", type text}, {"Custom.12", type text}, {"Custom.13", type text}, {"Custom.14", type text}, {"Custom.15", type text}, {"Custom.16", type text}, {"Custom.17", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"results"})
in
    #"Removed Columns"