Forum Discussion
Simple question, How to summarize rows
Hi, I have what should be a simple issue but I just can't seem to sort this one out (perhaps I need more coffee ☕🤣)
I have two columns, in my example data first a city and second a comment about it.
I want to summarize every city in a single row and list all the comments out along that row. I have been playing around with pivot, unpivot, transpose but just can't seem to get it. I am pretty sure I am just having a tough day of it and missing something simple, so decided to ask the community for help!!!
Here is an example of what I have...
Place Description
| New York | Loved it |
| New York | Nice |
| New York | Fantastic place |
| Pheonix | Brilliant |
| Los Angeles | Had a great time |
| Los Angeles | Great People |
| San Diego | Just Wow |
Here is what I am after
Place Description01 Description02 Description03
| New York | Loved it | Nice | Fantastic place |
| Pheonix | Brilliant | ||
| Los Angeles | Had a great time | Great People | |
| San Diego | Just Wow |
So each city appears just once but all the descriptions are listed out through the row....
Anyone out there able to help me out???
It depends a little, on what you want to achieve. Do you want to model the Data in PowerQuery to match this structure? Then try Grouping:
let Source = "Your Source!", #"Group" = Table.Group(Source, {"Place"}, {{"Description", each Text.Combine([Description],";")}}), #"Split Column" = Table.SplitColumn(#"Group", "Description", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Description.1", "Description.2", "Description.3"}) in #"Split Column"Edit: this will be the output in Table View:
If it is merely a Question of Visual representation try a Matrix and add both Data Fields to the "Row" Section.
4 Replies
It depends a little, on what you want to achieve. Do you want to model the Data in PowerQuery to match this structure? Then try Grouping:
let Source = "Your Source!", #"Group" = Table.Group(Source, {"Place"}, {{"Description", each Text.Combine([Description],";")}}), #"Split Column" = Table.SplitColumn(#"Group", "Description", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Description.1", "Description.2", "Description.3"}) in #"Split Column"Edit: this will be the output in Table View:
If it is merely a Question of Visual representation try a Matrix and add both Data Fields to the "Row" Section.
Thank you for taking the time to help me. I appreciate your help.
It looks like I needed to do this solution using M code as part of the GroupBy function. In particular
Text.Combine([Description],";")I could not work out how to do this from the GroupBy button.
Again, thank you for taking your time to help me.
Thanks, I really appreciate your help, but unfortunately it didn't work. I put City as Rows and Description of Values but it only gave the first description. Here is what happened.
It is missing the other descriptions..... This seems to be a tougher question than I originally thought..
