Combining multiple rows into one based on column values

[Sachintha]

I have a CSV table that looks like this:

Item,Event,Date,Comment
A,E1,1/23/2022,XYZ
A,E1,1/23/2022,ABC
A,E2,1/24/2022,KLM
A,E2,1/24/2022,NOP
A,E2,1/24/2022,DEF
B,E9,3/3/2022,XYZY
B,E9,3/3/2022,AAA
B,E3,3/5/2022,STU

Essentially, for the same { Item, Event, Date } combination there can be one or more comments. Think of it as comments entered by different users for the same combination above. 

I would like to group the comments into one cell (with perferably a line feed) and trim the table down to look something like this:

What's the best way to do this in Power Query?

[Daryl-Lynch-Bzy · Accepted solution]

Hi @Sachintha - you should consider using an advance group by function as described here : Advanced Group By Tricks in Power Query - YouTube.  The difference is the Text.Combine( _[Comment] , "#(lf)" )  function will use "#(lf) to maintain the separate line.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRxNdQx1Dcy1jcyMDLSiYiMUorVwRB2dHKGCRuBhE0gwt4+vtiE/fwDsAm7uLqBhZ10XC11jPURNkZiEXZ0dISJGgNFTSGiwSGhSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item,Event,Date,Comment" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Demoted Headers", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Item", "Event", "Date"}, {{"Comment", each Text.Combine(_[Comment] , "#(lf)" ) }})
in
    #"Grouped Rows"