Forum Discussion
Combining multiple rows into one based on column values
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?
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"
1 Reply
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"