Forum Discussion
Solved
Summarize with text
Hello everyone,
I have the table below
| Product | Amount | Store |
| Tomato | 10 | A |
| Carrot | 5 | A |
| Onion | 8 | A |
| Cucumber | 12 | A |
| Tomato | 8 | B |
| Carrot | 2 | B |
| Carrot | 15 | C |
| Onion | 13 | C |
and I need the final table using "Summarize"
Can you help me how to join the stores as in the table below?
| Product | Amount | Store |
| Tomato | 18 | A-B |
| Carrot | 22 | A-B-C |
| Onion | 21 | A-C |
| Cucumber | 12 | A |
Have you tried using the CONCATENATEX DAX command? How about this? (Sorry, replace "," with "-")
Medida: CONCATENATEX (Vegs, Vegs [Store], ",")
2 Replies
Have you tried using the CONCATENATEX DAX command? How about this? (Sorry, replace "," with "-")
Medida: CONCATENATEX (Vegs, Vegs [Store], ",")Hi JorgePereira ,
Try this mcode:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnPTSzJV9JRMjQAEo5KsTrRSs6JRUX5JUCuKVzIPy8zPw/Is0AoKk0uzU1KLQJpNYKLwo0DKXRCNc0IU8gQZIMzig2GxhChWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Amount = _t, Store = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Amount", Int64.Type}, {"Store", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, { {"Amount", each List.Sum([Amount]), type nullable number}, {"Store", each Text.Combine([Store], "-"), type text} } ) in #"Grouped Rows"
