Forum Discussion
Solved
get row values on same cell
Hello altruists,
I have table with different product's colour. I want for same product all colour to be in same cell in Power Query, separated by some character such as forward slash. Can someone help pls?
table I have
| Type | Produt | Colour |
| Fruit-- | apple-- | golden |
| Fruit-- | apple-- | red |
| Fruit-- | apple-- | green |
| Fruit-- | orange-- | yellow |
| Fruit-- | orange-- | orange |
| Vegetable-- | aubergine-- | navy |
| Vegetable-- | aubergine-- | white |
| Vegetable-- | tomato-- | red |
Table I want,
| Type | Produt | Colour |
| Fruit-- | apple-- | golden/red/green |
| Fruit-- | orange-- | yellow/orange |
| Vegetable-- | aubergine-- | navy/white |
| Vegetable-- | tomato-- | red |
sparvez Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc8xCoAwDAXQu3S21/AILtIhpSEWalNCtHh7wQoOVtx+eMmHzLMZZYtqrRkMlJLwSsQpYDZu6LNg+DQSfF2yQKbGB6bE9dtbvHxCQgV/98LmUSjmNmXYj9+lukTtVSmvoPx84k4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Produt = _t, Colour = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Produt", type text}, {"Colour", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Type", "Produt"}, {{"Colours", each _, type table [Type=nullable text, Produt=nullable text, Colour=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Lines.ToText(Table.ToList(Table.SelectColumns([Colours], "Colour")),"/")) in #"Added Custom"
3 Replies
sparvez Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc8xCoAwDAXQu3S21/AILtIhpSEWalNCtHh7wQoOVtx+eMmHzLMZZYtqrRkMlJLwSsQpYDZu6LNg+DQSfF2yQKbGB6bE9dtbvHxCQgV/98LmUSjmNmXYj9+lukTtVSmvoPx84k4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Produt = _t, Colour = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Produt", type text}, {"Colour", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Type", "Produt"}, {{"Colours", each _, type table [Type=nullable text, Produt=nullable text, Colour=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Lines.ToText(Table.ToList(Table.SelectColumns([Colours], "Colour")),"/")) in #"Added Custom"the previous one already worked
Thanks dude, it worked. Happy new year.
just there is a spelling mistake in last "Produt" - it has to be "Product". I tell it for others to correct the spelling.
