Combining values in multiple rows based on the Category

[newpbiuser01]

Hello,

I am trying to combine/concatenate values from different rows as long as they below to the same category. What I mean by that is, if we have a table with a list of shoppers, categories (fruit/vegetable) and the corresponding stuff they bought, I'd like to combine the data so that we get a list of the shoppers split by the category, where the type of fruits are combined in one row in a single column, and the colors are combined in a single row in the same column. 

So, if the original table is as follows:

Shopper	Category	Type	Color
Bob	Fruit	Apple	Red
Bob	Vegetable	Tomato	Red
Bob	Vegetable	Spinach	Green
Andy	Fruit	Cherry	Red
Andy	Fruit	Grape	Green
Joe	Fruit	Orange	Orange
Joe	Fruit	Apple	Red
Joe	Fruit	Banana	Yellow
Joe	Vegetable	Celery	Green
Susan	Vegetable	Potato	Yellow
Susan	Vegetable	Cucumber	Green

The expected result would be:

Shopper	Category	Type	Color
Bob	Fruit	Apple	Red
Bob	Vegetable	Tomato|Spinach	Red|Green
Andy	Fruit	Cherry|Grape	Red|Green
Joe	Fruit	Orange|Apple|Banana	Orange|Red|Yellow
Joe	Vegetable	Celery	Green
Susan	Vegetable	Potato|Cucumber	Yellow|Green

Would anyone know how I could go ahead and do that? Any help would be greatly appreciated. 

Thank you! 

[tackytechtom · Accepted solution]

Hi @newpbiuser01 ,

How about this:

Here the M code to paste into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDNCoMwEITfJWdfQgMVemmppVDEw6qDCjEJ24Ti2zdYijEtZQ87MB+zP3UtCtOKTBzYTy703FqF0C/oRZN93BsGOGpX52pmcuYvUtlJUzcGVTKgVyrX/RLNkSOYlygl8Usmi13A0SDyT0x6wCa+ifSSvVuQDhXEHUqZZ0TEh0gorFtua1T+QTrBzsa9XxKF/eKk7/zcgrfA5gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Shopper = _t, Category = _t, Type = _t, Color = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shopper", type text}, {"Category", type text}, {"Type", type text}, {"Color", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Shopper", "Category"}, {"Type", each Text.Combine([Type], "|"), type text}),
    #"Grouped Rows2" = Table.Group(#"Changed Type", {"Shopper", "Category"}, {"Color", each Text.Combine([Color], "|"), type text}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows1", {"Shopper", "Category"}, #"Grouped Rows2", {"Shopper", "Category"}, "Grouped Rows2", JoinKind.LeftOuter),
    #"Expanded Grouped Rows2" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows2", {"Color"}, {"Grouped Rows2.Color"})

in
    #"Expanded Grouped Rows2"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/