Combining values in multiple rows based on the Category

Question

Hello,&nbsp;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.&nbsp;&nbsp;So, if the original table is as follows:ShopperCategoryTypeColorBobFruitAppleRedBobVegetableTomatoRedBobVegetableSpinachGreenAndyFruitCherryRedAndyFruitGrapeGreenJoeFruitOrangeOrangeJoeFruitAppleRedJoeFruitBananaYellowJoeVegetableCeleryGreenSusanVegetablePotatoYellowSusanVegetableCucumberGreen&nbsp;The expected result would be:ShopperCategoryTypeColorBobFruitAppleRedBobVegetableTomato|SpinachRed|GreenAndyFruitCherry|GrapeRed|GreenJoeFruitOrange|Apple|BananaOrange|Red|YellowJoeVegetableCeleryGreenSusanVegetablePotato|CucumberYellow|Green&nbsp;Would anyone know how I could go ahead and do that? Any help would be greatly appreciated.&nbsp;Thank you!&nbsp;

tackytechtom · Accepted Answer

Hi&nbsp;newpbiuser01&nbsp;,&nbsp;How about this:&nbsp;&nbsp;Here the M code to paste into the advanced editor:&nbsp;&nbsp;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 🙂&nbsp;/Tomhttps://www.tackytech.blog/https://www.instagram.com/tackytechtom/

Forum Discussion

Combining values in multiple rows based on the Category

1 Reply

Related content

Power Query Combine Multiple Files From Folder

Tables combine

Combine values from multiple rows

Ranking Sales By Multiple Categories

Append/ Combine Multiple Json Files

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