Forum Discussion

Sachintha's avatar
Sachintha
Helper III
4 years ago
Solved

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,A...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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"




Anonymous's avatar
Anonymous
Not applicable
4 years ago

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"