Forum Discussion
Grouping or Transpose table
Hi folks,
Hope you are well. I am trying to group and transpose data as below. Can you let me know if there is a way to acheive this?
Actual table:
| Customer | Item Bought |
| AAA | Car |
| AAA | Bike |
| AAA | Cycle |
| BBB | Car |
| CCC | Car |
| CCC | Bike |
Desired Output:
| Customer | Item Bought |
| AAA | Car,Bike,Cycle |
| BBB | Car |
| CCC | Car,Bike |
Thanks for your time and help.
Anonymous Create below Measure
Measure = CONCATENATEX('Table','Table'[Item Bought],",")
or
Another way in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRck4sUorVgfGcMrNTkbjOlck5EL6TkxOSYmdnZwweRGssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Item Bought" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Item Bought", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Items", each Text.Combine([Item Bought],","), type nullable text}})
in
#"Grouped Rows"
2 Replies
Anonymous Create below Measure
Measure = CONCATENATEX('Table','Table'[Item Bought],",")
or
Another way in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRck4sUorVgfGcMrNTkbjOlck5EL6TkxOSYmdnZwweRGssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Item Bought" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Item Bought", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Items", each Text.Combine([Item Bought],","), type nullable text}})
in
#"Grouped Rows"Hi ddpl Thank you so much for your response, much appreciated