PQ - Concatenation by condition (XL2016)

[sandy666]

SourceResult

#OwnerMakeOwnerCar


1JeremyFordJeremyFord


2JillToyotaJillToyota


3JackToyotaJackToyota


4ArnoldFordArnoldFord,Holden,Mazda,Mercedes


5ArnoldHoldenSageToyota


6ArnoldMazdaPatrickToyota,Tesla


7ArnoldMercedesAlexUnknown


8SageToyotaRainTesla


9PatrickToyota


10PatrickTesla


11AlexUnknown


12RainTesla


Output must retain hierarchy that input has i.e. Jeremy must remain on top, Jill must remain the next, etc.



// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Grp = Table.Group(Source, {"Owner"}, {{"All", each _, type table [#"#"=number, Owner=text, Make=text]}}),
List = Table.AddColumn(Grp, "Car", each [All][Make]),
Ext = Table.TransformColumns(List, {"Car", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
Ext