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

[jagostinhoCT]

My query is producing the results below. Is there a way to convert it so that I have a single row for clashName 149 and all the left column values are spread out to its right, one on a separate column?

My current table has more clashName entries and I would like to do the same to each.

clashresults	clashName
File	Clash149
rac_advanced_sample_project.nwc	Clash149
<No level>	Clash149
Structural Framing	Clash149
Curved Beam	Clash149
Curved Beam	Clash149
Curved Beam	Clash149
Curved Beam	Clash149
Metal - Steel - ASTM A992	Clash149
File	Clash149
rac_advanced_sample_project.nwc	Clash149
01 - Entry Level	Clash149
Structural Columns	Clash149
M_W-Wide Flange-Column	Clash149
W250X49.1	Clash149
M_W-Wide Flange-Column	Clash149
Metal - Steel - 345 MPa	Clash149

Something like this... 

Clash149

File

rac_advanced_sample_project.nwc

<No level>

Structural Framing

Curved Beam

Curved Beam

Curved Beam

Curved Beam

Metal - Steel - ASTM A992

File

rac_advanced_sample_project.nwc

01 - Entry Level

Structural Columns

M_W-Wide Flange-Column

W250X49.1

M_W-Wide Flange-Column

Metal - Steel - 345 MPa

thank you

[v-piga-msft]

Hi  @jagostinhoCT,

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

[kamran_bsh · Accepted solution]

Hi

If I understand correctly, please use below steps

First Group by Clash name and  under operation use All rows

Then add a custom column using formula:

Text.Combine([Count][clashresults],", ")

This should give you the result you want. 

[Stachu]

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"